Join MCQs test row survival, NULL placement, repeated-key multiplication, and the Cartesian product behind a comma-separated FROM.
Attempt all 12 before checking each answer. Classify misses as row preservation, matching condition, multiplicity, or SQL meaning. If tracing joins from one fixed relation set is still unfamiliar, start with Inner and Outer Join MCQs: 12 Solved DBMS Questions. Then work without a fixed schema: SQL syntax, relational algebra, repeated keys, and row preservation each require a fresh result from the relations in the stem.
1. Inner and Outer Joins: The 90-Second Row-Preservation Map
Join | Rows kept |
|---|---|
| Matched pairs only |
| Every left row; unmatched right columns become |
| Every right row; unmatched left columns become |
| Matched pairs plus unmatched rows from both sides |
For Employee.dept_id = Department.dept_id, INNER keeps matches; LEFT, RIGHT, and FULL preserve the named sides. Unmatched columns become NULL. NATURAL JOIN uses every same-named column, while FROM R, S forms R x S before WHERE filters it.
2. Join-Type MCQs 1-3: Match Only, Preserve One Side, or Preserve Both
Question 1
Which SQL join returns all rows from both tables, with NULL values for non-matching rows ?
A. INNER JOIN.
B. LEFT JOIN
C. RIGHT JOIN
D. FULL OUTER JOIN
Answer: D. FULL OUTER JOIN keeps the intersection and unmatched rows from both inputs. In the example, INNER JOIN keeps 1 row, LEFT JOIN keeps 3, RIGHT JOIN keeps 2, and FULL OUTER JOIN keeps all 4.
Question 2
What is the difference between "INNER JOIN" and "LEFT JOIN" in SQL?
A. Both are the same
B. INNER JOIN returns all rows from both tables, LEFT JOIN returns only matching rows
C. INNER JOIN returns only matching rows from both tables, LEFT JOIN returns all rows from the left table and matching rows from the right table
D. LEFT JOIN can be used with a WHERE clause
Answer: C. Ravi's (2, 'Ravi', 20) disappears from the inner result but survives the left result with dept_name = NULL. Option D is not a defining difference because either join can appear in a query with WHERE.
Question 3
Which type of SQL JOIN automatically matches rows from two tables using columns that have the same name in both tables?
A. Inner Join
B. Cross Join
C. Self Join
D. Natural Join
E. Left Join
Answer: D. For Employee(dept_id, name) and Department(dept_id, dept_name), NATURAL JOIN discovers dept_id without an explicit ON. If both tables later gain location, that column also enters the match, so explicit ON is often safer.
3. Row-Preservation MCQs 4-6: Supersets, Lost Tuples, and NULL-Filled Rows
Question 4
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
Answer: D. Let Book = {(10, 'DBMS'), (20, 'OS')} and Stock = {(10, 5), (30, 7)}. Inner gives (10, 5), left adds (20, NULL), and right adds (NULL, 7). That last NULL is projected from missing B.isbn. Full contains all three, so Query 4 is the guaranteed superset.
Question 5
____ operation preserves those tuples that would be lost in _____.
A. natural join, outer join
B. outer join, natural join
C. left outer join, right outer join
D. left outer join, natural join
Answer: B. A natural matching join drops unmatched tuples. An outer join preserves one or both unmatched sides. D is too narrow because a left join cannot preserve the unmatched right-side department (30, 'HR').
Question 6
Consider two relations R₁(A,B) with the tuples (1,5), (3,7) and
R₂(A,C) = (1,7), (4,9). Assume that R(A,B,C) is the full natural outer join of R₁ and R₂. Consider the following tuples of the form (A,B,C):
a = (1,5,null), b = (1,null,7),
c = (3,null,9), d = (4,7,null),
e = (1,5,7), f = (3,7,null),
g = (4,null,9).
Which one of the following statements is correct?
A. R contains a, b, e, f, g but not c, d
B. R contains all of a, b, c, d, e, f, g
C. R contains e, f, g but not a, b
D. R contains e but not f, g
Answer: C. Common A=1 merges into e=(1,5,7). Unmatched A=3 becomes f=(3,7,null), and unmatched A=4 becomes g=(4,null,9). Tuples c and d wrongly combine unrelated keys.
4. Output-Tracing MCQs 7-9: Count Matches Before You Count Rows
Question 7
Suppose ORACLE relation 𝑅(𝐴,𝐵) currently has tuples {(1,2),(1,3),(3,4)} and relation 𝑆(𝐵,𝐶) currently has {(2,5),(4,6),(7,8)}.Consider the following two SQL queries SQ1 and SQ2:
SQ1: Select *
From R Full Join S
On R.B=S.B;
SQ2: Select *
From R Inner Join S
On R.B=S.B;
The numbers of tuples in the result of the SQL query SQ1 and the SQL query SQ2 are given by:
A. 2 and 6 respectively
B. 6 and 2 respectively
C. 2 and 4 respectively
D. 4 and 2 respectively
Answer: D. Keys 2 and 4 produce 2 inner rows: (1,2,5) and (3,4,6). Full also preserves (1,3,NULL) from R and (NULL,7,8) from S, giving 4 rows.
Question 8
What is the output of the following SQL query?
Employee
| Department
| OT_allowance
|
RAMA
| Mechanical
| 5000
|
GOPI
| Electrical
| 2000
|
SINDHU
| Computer
| 4000
|
MAHESH
| Civil
| 1500
|
SELECT COUNT(*)
FROM ((SELECT Employee, Department FROM Overtime_allowance) AS S
NATURAL JOIN
(SELECT Department, OT_allowance FROM Overtime_allowance) AS T);
A. 16
B. 4
C. 8
D. 2
Answer: B. Department is the only common column. Mechanical, Electrical, Computer, and Civil each occur once per side, so they contribute 1 x 1 each: 1 + 1 + 1 + 1 = 4, not a 4 x 4 Cartesian product. If Mechanical appeared twice in each projection, that department alone would contribute 2 x 2 = 4 joined rows; add these per-department products to get the total.
Question 9
Consider the following tables in a database called SPORTS.
GAMES
GID | GNAME | FEES
G01 | Football | 300
G02 | Cricket | 1000
G03 | Basketball | 500
PLAYERS
PID | PNAME | SEX | GID
P101 | Amrita | F | G03
P103 | Ameena | F | G02
P107 | Angel | F | G02
P109 | Aman | M | G03
Which is the best command to display the player name (PNAME) and the respective game name (GNAME)?
A. SELECT PNAME, GID FROM PLAYERS;
B. SELECT PNAME, GNAME FROM GAMES, PLAYERS WHERE GAMES.GID=PLAYERS.GID;
C. SELECT PNAME, GNAME FROM GAMES, PLAYERS;
D. SELECT PNAME, GNAME FROM GAMES, PLAYERS WHERE PLAYERS.PID=GAMES.GID;
Answer: B. The common identifier is GID. Results are Amrita, Basketball, Ameena, Cricket, Angel, Cricket, and Aman, Basketball. C gives 3 x 4 = 12 pairs, while D compares unrelated identifiers.
5. SQL-Semantics MCQs 10-12: Natural Join, Cartesian Product, and Self Join
Question 10
Consider the following statements:
S1: Natural join outputs all pairs of rows from the two input relations.
S2: Cartesian product outputs pairs of rows from the two input relations that have the same value on all attributes that have the same name.
Which of the following is CORRECT?
A. both S1 and S2 are false
B. S1 is true but S2 is false
C. both S1 and S2 are true
D. S1 is false but S2 is true
Answer: A. The definitions are exchanged. Two left rows and three right rows produce 2 x 3 = 6 Cartesian pairs without equality testing. Natural join retains only pairs equal on every common attribute.
Question 11
Consider the following SQL query
Select distinct a1, a2, …, an
from r1, r2, …, rm
where P
For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?
A. Πa1,a2,…,an σP (r1 × r2 × ⋯× rm)
B. Πa1,a2,…,an σP (r1⋈r2⋈⋯⋈rm)
C. Πa1,a2,…,an σP (r1∪r2∪⋯∪rm)
D. Πa1,a2,…,an σP (r1∩r2∩⋯∩rm)
Answer: A. FROM creates the Cartesian product, WHERE P applies selection σP, and SELECT DISTINCT projects with duplicate elimination. A join may optimise a suitable P, but it is not the direct equivalent for arbitrary P.
Question 12
What does the following SQL query return? SELECT DISTINCT T.branch_name FROM branch T, branch S WHERE T.assets > S.assets AND S.branch_city = 'HYDERABAD';
A. branches whose assets are greater than at least one branch located in HYDERABAD
B. branches whose assets are greater than every branch located in HYDERABAD
C. only the HYDERABAD branch with the greatest assets
D. branches whose assets are less than at least one branch located in HYDERABAD
Answer: A. This is a self join because branch appears twice under aliases T and S. Let Hyderabad assets be 40 and 70. Candidate 50 pairs with 40, so it qualifies despite not exceeding 70. Candidate 80 qualifies, while 30 does not. DISTINCT removes repeated names from multiple successful pairs.
6. Six Join Traps in SQL
Avoid six shortcuts: FULL is not a Cartesian product; repeated-key frequencies multiply; NATURAL JOIN uses every shared name; FROM R, S starts with R x S; and one successful pair proves existence, not universality. After a left join, a WHERE test on right-side columns can remove the preserved unmatched rows.
Next, use SQL Query MCQs: 12 Solved SELECT, Joins, and Subqueries for mixed SQL practice.
7. Answer Grid, Error Labels, and the Next Practice Step
Answer grid: 1-D, 2-C, 3-D, 4-D, 5-B, 6-C, 7-D, 8-B, 9-B, 10-A, 11-A, 12-A.
For each miss, identify the preserved side, list the shared columns, count matching pairs, and apply filters last.
Use Computer Science Fundamentals for Placements for the wider DBMS and core-CS sequence, or GATE Guidance by Sanchit Sir for structured GATE preparation. The live DBMS MCQ collection is the next subject-wide practice route.
Redo only the questions you missed, stating the row-preservation rule before choosing again.
