SQL Joins Explained: INNER, LEFT, RIGHT, FULL, CROSS and SELF with Runnable Examples

SQL join diagrams hide the part that matters in an output question: one key can create several pairs, and an unmatched row survives only when its side is preserved. A six-agent support dataset makes every pair, NULL and result count traceable. PostgreSQL runs every query as written; engines without RIGHT or FULL need equivalent rewrites.

SQL joins: what a join actually does

A join is a rule for pairing rows from table references. The ON clause defines a match. The join type decides which unmatched rows survive, and a later WHERE clause filters the rows already produced.

• INNER JOIN keeps matching pairs only.

• LEFT JOIN keeps every row from the left input.

• RIGHT JOIN keeps every row from the right input.

• FULL OUTER JOIN keeps unmatched rows from both inputs.

• CROSS JOIN emits every possible pair.

• A self join gives two aliases to the same table so its rows can relate to one another.

Pair-counting and relational-algebra reasoning are developed in Join Operations in DBMS: Inner, Left, Right and Full Outer Joins Worked Step by Step. The six-join map adds a Cartesian product and a mentor relationship to that preserved-side foundation.

Build one runnable support-agent and queue dataset

Run the setup once. The missing foreign key is deliberate: Meera can carry queue 40 even though that queue is absent, while Kabir can carry a NULL queue.

CREATE TABLE support_queues (
  queue_id INTEGER PRIMARY KEY,
  queue_name VARCHAR(40) NOT NULL
);

CREATE TABLE support_agents (
  agent_id INTEGER PRIMARY KEY,
  agent_name VARCHAR(40) NOT NULL,
  queue_id INTEGER,
  mentor_id INTEGER
);

INSERT INTO support_queues (queue_id, queue_name) VALUES
  (10, 'Billing'),
  (20, 'Technical'),
  (30, 'Retention');

INSERT INTO support_agents (agent_id, agent_name, queue_id, mentor_id) VALUES
  (201, 'Ayaan',  10, NULL),
  (202, 'Diya',   20, 201),
  (203, 'Farah',  10, 201),
  (204, 'Kabir', NULL, 202),
  (205, 'Meera',  40, 201),
  (206, 'Nikhil', 20, 202);

Billing and Technical each match two agents. Retention has no agent, queue 40 exists only in the agent table, and Kabir supplies no queue value. These four facts determine every output below.

INNER JOIN: keep only matching agent-queue pairs

SELECT a.agent_id, a.agent_name, q.queue_name
FROM support_agents AS a
INNER JOIN support_queues AS q ON a.queue_id = q.queue_id
ORDER BY a.agent_id;

The result has four rows. Kabir fails because equality with NULL is not TRUE, Meera has no queue 40 to match, and Retention has no agent. Billing and Technical repeat because each queue row qualifies against two different agent rows.

LEFT, RIGHT and FULL OUTER JOIN: choose which unmatched rows survive

A LEFT JOIN preserves all six agents. Kabir and Meera remain, but their projected queue names are NULL. A RIGHT JOIN preserves the three queue rows, so its four matches plus the unmatched Retention row produce five rows.

SELECT a.agent_name,
       COALESCE(a.queue_id, q.queue_id) AS queue_id,
       q.queue_name
FROM support_agents AS a
FULL OUTER JOIN support_queues AS q ON a.queue_id = q.queue_id
ORDER BY COALESCE(a.queue_id, q.queue_id, 999), a.agent_id;

FULL returns 4 matched pairs + 2 unmatched agents + 1 unmatched queue = 7 rows. The dedicated SQL OUTER JOINs: LEFT, RIGHT, and FULL with Worked Examples owns the longer engine-support and predicate-placement treatment; this table keeps the six-type comparison on one dataset.

CROSS JOIN and SELF JOIN: products and mentor relationships

A CROSS JOIN has no matching condition. Six agents multiplied by three queues gives 6 x 3 = 18 rows.

SELECT a.agent_name, q.queue_name
FROM support_agents AS a
CROSS JOIN support_queues AS q
ORDER BY a.agent_id, q.queue_id;

The first six rows are Ayaan-Billing, Ayaan-Technical, Ayaan-Retention, Diya-Billing, Diya-Technical and Diya-Retention. Farah, Kabir, Meera and Nikhil each contribute the same three-queue pattern, completing the exact 18-row product.

A self join uses aliases to assign two roles to one stored table:

SELECT a.agent_name AS agent, m.agent_name AS mentor
FROM support_agents AS a
INNER JOIN support_agents AS m ON a.mentor_id = m.agent_id
ORDER BY a.agent_id;

Ayaan is absent because the root mentor_id is NULL. A left self join would retain Ayaan with a NULL mentor. For peer comparisons, unique pairs and revision histories, continue with Self Join in SQL: Syntax, Worked Examples, and Exercises; the mentor output above is the hierarchy case only.

SQL join traps: NULL, filter placement and misleading counts

These left joins place the same Technical predicate at different stages:

-- Filters the joined result: Diya and Nikhil only
SELECT a.agent_name, q.queue_name
FROM support_agents AS a
LEFT JOIN support_queues AS q ON a.queue_id = q.queue_id
WHERE q.queue_name = 'Technical';

-- Restricts matches: all six agents survive
SELECT a.agent_name, q.queue_name
FROM support_agents AS a
LEFT JOIN support_queues AS q
  ON a.queue_id = q.queue_id AND q.queue_name = 'Technical';

The first query returns only Diya and Nikhil. The second returns all six agents; only Diya and Nikhil carry Technical, while the other four queue-name cells are NULL. A right-table predicate in WHERE can therefore remove the unmatched rows a left join preserved.

SELECT q.queue_name,
       COUNT(*) AS result_rows,
       COUNT(a.agent_id) AS matched_agents
FROM support_queues AS q
LEFT JOIN support_agents AS a ON q.queue_id = a.queue_id
GROUP BY q.queue_id, q.queue_name
ORDER BY q.queue_id;

COUNT(*) includes Retention's preserved placeholder row. COUNT(a.agent_id) ignores its NULL agent key. Also use IS NULL rather than = NULL, qualify shared column names, and inspect key multiplicity before treating repeated values as duplicates.

SQL join practice: four output-prediction questions

• How many INNER JOIN rows appear? Four, one for each agent whose queue key exists.

• How many FULL OUTER JOIN rows appear? Seven: four matches, two agent-only rows and one queue-only row.

• How can Retention survive when support_agents is written first? Use RIGHT JOIN, or reverse the table order and use LEFT JOIN.

• What does the left join with WHERE q.queue_name = Technical return? Diya and Nikhil only.

Each answer follows from the stored keys. Recompute the pairs on paper before running the SQL, then change one row and predict the effect before executing again.

SQL join cheat sheet and the next practice step

Start with the rows that must survive, then choose INNER, LEFT, RIGHT or FULL. Use CROSS only when every combination is intentional, and use aliases whenever one table plays two roles. Predict result size from matching key groups before adding WHERE, GROUP BY or aggregates.

Insert queue (40, Support) and predict the new counts: INNER becomes 5, LEFT remains 6, RIGHT becomes 6, FULL becomes 7, CROSS becomes 24, and the mentor self join remains 5. Run the queries to verify. For timed DBMS practice, use the GATE Test Series and browse the GATE CS Exam Preparation Courses and Test Series category.