250+ TOP MCQs on SQL Server Joins and Answers

SQL Server Multiple Choice Questions on “Joins”.

1. What type of join is needed when you wish to include rows that do not have matching values?
a) Equi-join
b) Natural join
c) Outer join
d) All of the Mentioned

Answer: c
Clarification:OUTER JOIN is the only join which shows the unmatched rows.

2. What type of join is needed when you wish to return rows that do have matching values?
a) Equi-join
b) Natural join
c) Outer join
d) All of the Mentioned

Answer: d
Clarification: Outer join returns the row having matching as well as non matching values.

3. Which of the following is one of the basic approaches for joining tables?
a) Subqueries
b) Union Join
c) Natural join
d) All of the Mentioned

Answer: d
Clarification: The SQL subquery is a SELECT query that is embedded in the main SELECT statement. In many cases, a subquery can be used instead of a JOIN.

4. The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T WHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID?
a) Equi-join
b) Natural join
c) Outer join
d) Cartesian join

Answer: a
Clarification: Equi-join joins only same data entry field. For example, one table contains department id and another table should contain department id.

5. A UNION query is which of the following?
a) Combines the output from no more than two queries and must include the same number of columns
b) Combines the output from no more than two queries and does not include the same number of columns
c) Combines the output from multiple queries and must include the same number of columns
d) Combines the output from multiple queries and does not include the same number of columns

Answer: c
Clarification: A single UNION can combine only 2 sql query at a time.

6. Which of the following statements is true concerning subqueries?
a) Involves the use of an inner and outer query
b) Cannot return the same result as a query that is not a subquery
c) Does not start with the word SELECT
d) All of the mentioned

Answer: a
Clarification: Subquery—also referred to as an inner query or inner select—is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery.

7. Which of the following is a correlated subquery?
a) Uses the result of an inner query to determine the processing of an outer query
b) Uses the result of an outer query to determine the processing of an inner query
c) Uses the result of an inner query to determine the processing of an inner query
d) Uses the result of an outer query to determine the processing of an outer query

Answer: a
Clarification: A ‘correlated subquery’ is a term used for specific types of queries in SQL in computer databases. It is a subquery (a query nested inside another query) that uses values from the outer query in its WHERE clause.

8. How many tables may be included with a join?
a) One
b) Two
c) Three
d) All of the Mentioned

Answer: d
Clarification: Join can be used for more than one table. For ‘n’ tables the no of join conditions required are ‘n-1’.

9. The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T?
a) Equi-join
b) Natural join
c) Outer join
d) Cartesian join

Answer: d
Clarification: Cartesian Join is simply the joining of one or more table which returns the product of all the rows in these tables.

10. Which is not a type of join in T-SQL?
a) Equi-join
b) Natural join
c) Outer join
d) Cartesian join

Answer: b
Clarification: A NATURAL JOIN is an inner join where the RDBMS automatically selects the join columns based on common columns names. Some RDBMS vendors, like Oracle but not SQL Server, implement a NATURAL JOIN operator.