Question

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?

Accepted Answer

The correct answer is (D) Query 4. A FULL OUTER JOIN returns every row that an INNER JOIN would (the matching rows), PLUS every unmatched row from the left table (like a LEFT OUTER JOIN) AND every unmatched row from the right table (like a RIGHT OUTER JOIN) — it is, by construction, always a superset of all three other join results.

Explanation