GATE CS 2018 Q12 — Tuple Calculus and SQL

To determine which query's output is guaranteed to be a superset of the others, let us analyze the behavior of the four SQL joins on the tables Book (B) and Stock (S) under the join condition B.isbn = S.isbn:

• Query 1 (INNER JOIN): Returns only the rows where there is a match in both tables.
  Let this result set be $Q_1$.
• Query 2 (LEFT OUTER JOIN): Returns all matching rows ($Q_1$) plus the unmatched rows from the left table (Book), padded with NULL values for the right table's columns.
  Let this result set be $Q_2$. Clearly, $Q_1 \subseteq Q_2$.
• Query 3 (RIGHT OUTER JOIN): Returns all matching rows ($Q_1$) plus the unmatched rows from the right table (Stock), padded with NULL values for the left table's columns.
  Let this result set be $Q_3$. Clearly, $Q_1 \subseteq Q_3$.
• Query 4 (FULL OUTER JOIN): Returns all matching rows, all unmatched rows from the left table, and all unmatched rows from the right table.
  Let this result set be $Q_4$.

By definition of these outer joins, we have:

This implies:

• $Q_1 \subseteq Q_4$
• $Q_2 \subseteq Q_4$
• $Q_3 \subseteq Q_4$

Thus, the output of Query 4 is always a superset of the outputs of the other three queries.