Consider the following two tables and four queries in SQL. Book (isbn, bname),…
2018
Consider the following two tables and four queries in SQL.
Book (isbn, bname), Stock (isbn, copies)
Query 1: SELECT B.isbn, S.copies
FROM Book B INNER JOIN Stock S
ON B.isbn = S.isbn;
Query 2: SELECT B.isbn, S.copies
FROM Book B LEFT OUTER JOIN Stock S
ON B.isbn = S.isbn;
Query 3: SELECT B.isbn, S.copies
FROM Book B RIGHT OUTER JOIN Stock S
ON B.isbn = S.isbn;
Query 4: SELECT B.isbn, S.copies
FROM Book B FULL OUTER JOIN Stock S
ON B.isbn = S.isbn;
Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?
- A.
Query 1
- B.
Query 2
- C.
Query 3
- D.
Query 4
Attempted by 438 students.
Show answer & explanation
Correct answer: D
Key idea: a FULL OUTER JOIN returns all matching rows plus rows that appear only in one table or the other.
INNER JOIN (Query 1) returns only matching Book–Stock pairs, so its output is a subset of any outer join that also includes matches.
LEFT OUTER JOIN (Query 2) returns all Book rows plus matching Stock rows; it can miss Stock-only rows.
RIGHT OUTER JOIN (Query 3) returns all Stock rows plus matching Book rows; it can miss Book-only rows.
FULL OUTER JOIN (Query 4) returns matching rows, Book-only rows, and Stock-only rows, so it contains everything returned by the other three joins.
Conclusion: The query that uses FULL OUTER JOIN is certain to produce an output that is a superset of the outputs of the INNER, LEFT OUTER, and RIGHT OUTER joins. When there is no matching row on one side, the projected columns from that side (for example, S.copies when there is no matching Stock) will be NULL.
A video solution is available for this question — log in and enroll to watch it.