250+ TOP MCQs on Performing Multiple – Table Retrievals with Joins and Answers

MySQL Database Multiple Choice Questions on “Performing Multiple – Table Retrievals with Joins”.

1. SELECT select_list FROM table_list WHERE row_constraint GROUP BY grouping_columns; Which of these is not optional?
a) select_list
b) table_list
c) row_constraint
d) grouping_columns

Answer: a
Clarification: Given above was a basic syntax of the SELECT statement. Everything in the syntax is optional except the ‘select_list’ option. All the others are free to be omitted, and will work fine.

2. In inner join, result is produced by matching rows in one table with rows in another table.
a) True
b) False

Answer: a
Clarification: The inner join is a form of join in MySQL that is used to combine the result of concatenating the contents of two tables into a new table. In inner join, result is produced by matching rows in one table with rows in another table.

3. The join where all possible row combinations are produced is called _________
a) INNER JOIN
b) OUTER
c) NATURAL
d) CARTESIAN

Answer: d
Clarification: In ‘cartesian product’, each row of each table is combined with each row in every other table to produce all possible combination. This produces a very large number of rows since the number is the product of rows.

4. The clause that filters JOIN results is called _________
a) WHERE
b) SORT
c) GROUP
d) GROUP BY

Answer: a
Clarification: Sometimes the result of a join is very large and is not desirable. In these cases, the results can be filtered with the help of the ‘WHERE’ clause which is followed by a set of condition(s).

5. CROSS JOIN and JOIN are similar to __________
a) INNER JOIN
b) NATURAL JOIN
c) OUTER JOIN
d) CARTESIAN JOIN

Answer: a
Clarification: The joins ‘CROSS JOIN’ and ‘JOIN’ types are exactly similar to the ‘INNER JOIN’. The statements containing ‘INNER JOIN’ can replace it with ‘CROSS JOIN’ or ‘JOIN’ to get exactly the same result.

6. The comma operator can also be used to join tables.
a) True
b) False

Answer: a
Clarification: The comma (,) operator can be used to join the tables as well. It joins them in the ‘INNER JOIN’ type. It is not desirable to use the comma operator since it has different precedence rules.

7. The left and right joins are also known as __________
a) INNER JOIN
b) NATURAL JOIN
c) OUTER JOIN
d) CARTESIAN JOIN

Answer: c
Clarification: The ‘inner join’ only deals with rows where a match can be found in both tables. The ‘LEFT JOIN’ and ‘RIGHT JOIN’ types are ‘OUTER JOIN’ types which differ from inner joins in this sense.

8. What is joining a table to itself called?
a) COMPLETE
b) SELF
c) OBSOLETE
d) CROSS

Answer: b
Clarification: Joining a table to itself in a database is called ‘self-join’. When a self-join is being performed, the table is being used multiple times within the query and a table name qualifier is unnecessary.

9. In which join all the rows from the left table appear in the output irrespective of the content of the other table?
a) RIGHT JOIN
b) LEFT JOIN
c) INNER JOIN
d) OUTER JOIN

Answer: b
Clarification: In a ‘LEFT JOIN’, the output is produced for every row of the left table, even if it does not exist in the right table. This is the reason it is called a ‘LEFT JOIN’. ‘LEFT JOIN’ is a kind of OUTER JOIN.

10. The join in which all the rows from the right table appear in the output irrespective of the content of the other table is ___________
a) CARTESIAN JOIN
b) CROSS JOIN
c) INNER JOIN
d) RIGHT JOIN

Answer: d
Clarification: In a ‘RIGHT JOIN’, the output is produced for every row of the right table, even if it does not exist in the other table. This is the reason why it is called a ‘RIGHT JOIN’. ‘RIGHT JOIN’ and ‘LEFT JOIN’ are a kind of OUTER JOIN.