- LPU MCA Semester 1
- CAP570
- Unit 4
CAP570 · Unit 4: Database joins
Inner, left, right and self joins
12 practice questions · in the ETE syllabus
Key points
- Inner keeps matches; left, right and full keep unmatched rows
- A self join uses two aliases of one table
- LEFT JOIN with IS NULL finds rows with no partner
- ON decides matches; WHERE removes rows after the join
Join types differ in which unmatched rows survive.
| Join | Result |
|---|---|
INNER JOIN | Only rows that match in both tables |
LEFT [OUTER] JOIN | All left rows; unmatched right columns are NULL |
RIGHT [OUTER] JOIN | All right rows; unmatched left columns are NULL |
FULL [OUTER] JOIN | Unmatched rows from both sides, with NULLs |
| Self join | A table joined to itself using two aliases |
Example: with students and departments, an inner join lists only students whose department exists, a left join also lists students without a department (department columns NULL), and a right join also lists departments without students. A self join is used for hierarchies such as employee and manager: FROM emp e JOIN emp m ON e.mgr = m.eid, where e is the employee and m the manager. Use a left self join to keep the top employee whose manager is NULL.
Useful patterns: LEFT JOIN ... WHERE right.key IS NULL finds rows with no partner. COUNT(*) counts all rows, but COUNT(column) skips NULLs. A condition in the ON clause only decides which rows match, so left rows are kept, while the same condition in WHERE removes rows after the join and turns a left join into an inner join. NULL never equals NULL in a join condition.
Exam trap: a WHERE filter on the right table of a left join discards the unmatched rows the join was meant to keep.
Try two questions
Which join returns only the rows that have matching values in both tables?
- Left outer join
- Inner join
- Full outer join
- Cross join
Show the answer
B. An inner join keeps only rows for which the condition is true on both sides.
Why not A: A left outer join also keeps unmatched rows of the left table.
Why not C: A full outer join keeps unmatched rows from both tables.
Why not D: A cross join pairs every row with every row and has no matching condition.
Which join keeps every row of the left table even when there is no match in the right table?
- Inner join
- Right outer join
- Left outer join
- Self join
Show the answer
C. In a left join unmatched left rows appear with NULL in the right-hand columns.
Why not A: An inner join drops rows without a match.
Why not B: A right outer join keeps every row of the right table instead.
Why not D: A self join joins a table with itself and says nothing about unmatched rows.