Skip to content
UniCircle
Menu
Sign inJoin
GuideEngineeringB.Tech / B.E. (CSE, IT)

SQL joins explained with two small tables

Inner, left, right, full and self joins shown on the same two tiny tables, so you can see exactly which rows each one keeps.

By UniCircle team2 min readPublished 22 Oct 2026Undergraduate

A join puts rows from two tables side by side when a condition matches. The kinds of join differ only in what happens to rows that have no match.

The two tables

SQL
CREATE TABLE students (id int, name text, club_id int);
CREATE TABLE clubs (id int, club text);
INSERT INTO students VALUES (1, 'Asha', 10), (2, 'Ravi', 20), (3, 'Meena', NULL);
INSERT INTO clubs VALUES (10, 'Robotics'), (20, 'Music'), (30, 'Drama');
SELECT s.name, c.club FROM students s JOIN clubs c ON c.id = s.club_id ORDER BY s.name;
text
Asha|Robotics
Ravi|Music

An inner join (plain JOIN) keeps only matching pairs. Meena has no club and Drama has no students, so both disappear.

Keeping the unmatched rows

SQL
CREATE TABLE students (id int, name text, club_id int);
CREATE TABLE clubs (id int, club text);
INSERT INTO students VALUES (1, 'Asha', 10), (2, 'Ravi', 20), (3, 'Meena', NULL);
INSERT INTO clubs VALUES (10, 'Robotics'), (20, 'Music'), (30, 'Drama');
SELECT s.name, c.club FROM students s LEFT JOIN clubs c ON c.id = s.club_id ORDER BY s.name;
SELECT c.club FROM clubs c LEFT JOIN students s ON s.club_id = c.id WHERE s.id IS NULL;
text
Asha|Robotics
Meena|
Ravi|Music
Drama
  • LEFT JOIN keeps every row of the left table and fills the right side with NULL when nothing matches (Meena).
  • RIGHT JOIN is the same with the sides swapped. Most people simply swap the tables and use LEFT JOIN.
  • FULL JOIN keeps unmatched rows from both sides.
  • The second query is the classic "find rows with no partner" pattern: LEFT JOIN, then keep the rows where the right side is NULL.

Self join: a table joined to itself

SQL
CREATE TABLE emp (id int, name text, manager_id int);
INSERT INTO emp VALUES (1, 'Kavya', NULL), (2, 'Arjun', 1), (3, 'Divya', 1);
SELECT e.name, m.name AS manager FROM emp e JOIN emp m ON m.id = e.manager_id ORDER BY e.name;
text
Arjun|Kavya
Divya|Kavya

Give the two copies different aliases (e and m) and the query reads naturally: each employee next to their manager.

#sql#joins#left join#self join

Sources and further reading

Practise this as an interview question

Written by the UniCircle team, checked by an editor on 22 Oct 2026. It is not official policy of any university. Exam patterns and rules change; confirm dates and rules on the official website.

Keep reading