GATE CS 2018 Q.22 (CS Q.12): Relational Algebra and Tuple Relational Calculus

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?

Query 1

Query 2

Query 3

Query 4

Official answer and solution

Official answer: D
From: Official GATE 2018 answer key

Solution

Compare tuple containment across inner, left outer, right outer, and full outer joins.

Let $B$ and $S$ denote the Book and Stock relations joined on isbn:

Query 1 (INNER JOIN): Retains only matching tuples where B.isbn = S.isbn.
Query 2 (LEFT OUTER JOIN): Retains all matching tuples plus all unmatched tuples from Book padded with NULLs for Stock attributes.
Query 3 (RIGHT OUTER JOIN): Retains all matching tuples plus all unmatched tuples from Stock padded with NULLs for Book attributes.
Query 4 (FULL OUTER JOIN): Retains all matching tuples, all unmatched tuples from Book, and all unmatched tuples from Stock.

By definition, the set of rows returned by FULL OUTER JOIN contains all rows produced by INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN. Therefore, Query 4 is guaranteed to be a superset of the outputs of the other three queries.

Final answer

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2018 Computer Science and Information Technology (CS)
Question: 22 of 65, printed as CS Q.12
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source