Inner and Outer Join MCQs: 12 Solved DBMS Questions with Explanations

Join distractors test three decisions: which rows survive, how many rows can be produced, and whether common columns count once or twice. The twelve questions below come from GATE, UGC NET, ISRO, TPSC and Coal India papers, and they move from inner and natural join definitions through key-constrained size bounds to outer-join padding and one left-outer-join equivalence trap. Choose an option before reading each explanation, and name the row-survival rule or cardinality equation you used. The Join Operations practice set carries over 70 questions on inner and outer joins, and the GATE CS Exam Preparation page lists the DBMS courses that cover them.

1. Inner and outer joins: one worked relation set to trace

An inner join keeps matching pairs. A natural join tests all identically named attributes and keeps each common attribute once. A left outer join adds unmatched left rows with right-side NULL; right outer reverses the sides, and full outer preserves both.

Reference relations:

B-join outputs:

Without keys, a condition join over m and n tuples ranges from 0 to mn rows. A natural join of degrees p and q with k shared names has degree p + q - k. Use SQL Queries and Joins in DBMS: Worked Join and GROUP BY as the SQL companion, not a replacement for tuple tracing.

2. Inner join MCQs: the definition and the size bounds

Question 1: ISRO 2008

The join operation can be defined as

• A. a cartesian product of two relations followed by a selection

• B. a cartesian product of two relations

• C. a union of two relations followed by cartesian product of the two relations

• D. a union of two relations

Answer: A. A theta join is selection(R × S): form pairs, then retain those satisfying the predicate. Union does not construct matching pairs. Source

Question 2: UGC NET 2018

In RDBMS, which type of Join returns all rows that satisfy the join condition?

• A. Inner Join

• B. Outer Join

• C. Semi Join

• D. Anti Join

Answer: A. Inner join returns rows satisfying the condition. Outer join preserves unmatched rows; semi and anti joins return rows from one side. Source

Question 3: GATE 1999

Consider the join of a relation R with a relation S. If R has m tuples and S has n tuples, then the maximum and minimum sizes of the join respectively are:

• A. m+n and 0

• B. mn and 0

• C. m+n and |m-n|

• D. mn and m+n

Answer: B. Unconstrained, all m × n pairs may satisfy the predicate, while no pair may satisfy it. Range: 0 to mn. Source

3. Natural join MCQs: keys, degree and exact counts

Question 4: TPSC 2024

Consider the relations r₁(P, Q, R) and r₂(R, S, T) with primary keys P and R respectively. The relation r₁ contains 2000 tuples and r₂ contains 2500 tuples. The maximum size of the join r₁ ⋈ r₂ is

• A. 2000

• B. 2500

• C. 4000

• D. 5000

Answer: A. Since attribute R is the key of r₂, each r₁ tuple matches at most one r₂ tuple. The maximum is 2,000. Source

Question 5: GATE 2010

The following functional dependencies hold for relations R(A, B, C) and S(B, D, E):

B → A

A → C

The relation R contains 200 tuples and the relation S contains 100 tuples. What is the maximum number of tuples possible in the natural join R ⋈ S?

• A. 100

• B. 200

• C. 300

• D. 2000

Answer: A. Transitivity gives B → A,C, so B is a key of R. Each of the 100 S tuples can match at most one R tuple. Source

Question 6: Coal India 2017

Considering the relation schemas R (A, B, C, D) and S (C, D, E, F), what will be the degree of the resultant relation of the following relational algebra expression, where ⨝ represents the natural join operation?

(σ(A=1)(R)) ⨝ (π(C,D,E)(S))

• A. 3

• B. 4

• C. 5

• D. 6

Answer: C. Selection leaves four columns, projection gives three, and common columns C,D count once: 4 + 3 - 2 = 5. Source

Question 7: GATE 2004

Consider the following relation schema pertaining to a students database:

Student (rollno, name, address)

Enroll (rollno, courseno, coursename)

where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join?

• A. 8, 8

• B. 120, 8

• C. 960, 8

• D. 960, 120

Answer: A. Every Enroll.rollno value must exist in Student.rollno, so each of the eight enrolments matches exactly one student row. That fixes both the maximum and the minimum at 8. Source

4. Outer join MCQs: unmatched rows and NULL padding

Question 8: UGC NET 2014

Which of the following is true?

I. Implementation of self-join is possible in SQL with table alias.

II. Outer-join operation is basic operation in relational algebra.

III. Natural join and outer join operations are equivalent.

• A. I and II are correct.

• B. II and III are correct.

• C. Only III is correct.

• D. Only I is correct.

Answer: D. Statement I holds: a table alias lets one table be joined to itself. Statement II fails, because outer join is derived from the basic relational algebra operations rather than being one of them. Statement III fails, because a natural join drops unmatched tuples while an outer join preserves them. Source

Question 9: UGC NET 2023

Consider the following relations X(S, Si, C) and Y(S, P, D). Which of the following join is used to get all the tuples of relation X and Y with Null values of corresponding missing values?

• A. Left outer join

• B. Right outer join

• C. Natural join

• D. Full outer join

Answer: D. Full outer join preserves unmatched rows from both relations and fills unavailable attributes with NULL. Left or right preserves one named side. Source

5. Left and full outer join MCQs: trace the tuples

Question 10: UGC NET 2016

Suppose table T1(P, R) currently has tuples (10,5), (15,8), (25,6) and table T2(A, C) currently has (10,6), (25,3), (10,5). Consider the following three relational algebra queries RA1, RA2 and RA3:

RA1: T1 ⋈ T2 with the join condition T1.P = T2.A

RA2: T1 left outer join T2 with the join condition T1.P = T2.A

RA3: T1 ⋈ T2 with the join conditions T1.P = T2.A and T1.R = T2.C

The number of tuples in the resulting tables of RA1, RA2 and RA3 are given by:

• A. 2,4,2 respectively

• B. 2,3,2 respectively

• C. 3,3,1 respectively

• D. 3,4,1 respectively

Answer: D. P=10 has two matches and P=25 one, so RA1=3. Preserving (15,8) gives RA2=4. Only (10,5) pairs with (10,5) under both predicates, so RA3=1. Source

Question 11: GATE 2015

Consider two relations R₁(A, B) with the tuples (1,5), (3,7) and R₂(A, C) with the tuples (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. A=1 produces e only. The unmatched left and right rows produce f and g. A matched key is not emitted again as padded rows a and b. Source

6. Left outer join equivalence: test where selection occurs

Question 12: GATE 2018

Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query

Q: r ⋈ (σ(B<5)(s))

Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values. Which one of the following queries is NOT equivalent to Q?

• A. σ(B<5)(r ⋈ s)

• B. σ(B<5)(r LOJ s)

• C. r LOJ (σ(B<5)(s))

• D. σ(B<5)(r) LOJ s

Answer: C. Let s={(4,'x'),(6,'y')} and r={(1,4),(2,6)}. Query Q keeps only the B=4 match. Option C filters s first, then preserves (2,6) with right-side NULL. The foreign key makes A, B and D equivalent to Q. Source

For every join, write the common attributes and predicate, mark keys and foreign keys, enumerate matches, then add unmatched rows. Insert NULL only after the inner result is complete. Count columns with p + q - k separately from counting tuples.

7. Inner and outer join MCQs: score check and next step

Use this only as a study check. At 10 to 12 correct, redo missed rules. At 7 to 9, retrace Questions 3 to 7 and 10 to 12. At 0 to 6, rebuild the reference relations' five output rows.

Then use DBMS MCQs for mixed-topic practice. Continue with GATE Guidance by Sanchit Sir after solving Questions 5, 7, 10, 11 and 12 without notes.

Remember: inner join keeps matches; outer join then preserves the required unmatched side. Keys and functional dependencies tighten 0..mn; natural join counts common columns once; moving selection below an outer join can change row survival. Finally, reproduce Section 1's five-row full outer result and Question 10's (3,4,1) count without looking. Correct traces show that you are applying the rules, not guessing from symbols.