Lesson 3 of 4

Progress: 0/3 exercises solved (0%)

Solve all exercises below to unlock the next lesson.

RIGHT JOIN and FULL OUTER JOIN

Lesson Description

Learn how to connect related tables safely so your results stay accurate, readable, and business-ready.

Easy Project

Mini project: Build a people-and-departments report that lists employee names, department names, and validates row counts to prove your join is correct.

JOIN Visual

employees

departments

Match keys first, then verify row counts after the join.

RIGHT JOIN

RIGHT JOIN keeps all rows from the right table. Unmatched left-side columns are NULL.

SELECT c.first_name, o.id AS order_id
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id;

This returns every customer, even those with no orders — identical to a LEFT JOIN with the tables swapped. Most engineers prefer LEFT JOIN for consistency; RIGHT JOIN is included for completeness.

FULL OUTER JOIN

FULL OUTER JOIN keeps all rows from both tables. Unmatched rows get NULLs on the missing side.

SELECT
  e.first_name  AS employee,
  d.name        AS department
FROM employees e
FULL OUTER JOIN departments d ON d.id = e.department_id;

Rows appear if they match, if only the employee side matches, or if only the department side matches.

Finding unmatched rows on both sides

SELECT e.first_name, d.name
FROM employees e
FULL OUTER JOIN departments d ON d.id = e.department_id
WHERE e.id IS NULL OR d.id IS NULL;

Join type comparison

Join	Keeps
INNER JOIN	Only matching rows
LEFT JOIN	All left + matched right
RIGHT JOIN	All right + matched left
FULL OUTER JOIN	All rows from both sides

Tips

FULL OUTER JOIN is rare in practice. Common use: reconciling two tables to find discrepancies.
Rewrite RIGHT JOIN as LEFT JOIN by swapping table order — your code stays consistent.
After FULL OUTER JOIN, always alias columns carefully — you may have NULLs from either side.

Learning context

This lesson is part of Joins. Focus on the core idea in RIGHT JOIN and FULL OUTER JOIN, then validate with deliberate practice.

What to master

Choose the base table (one row = what?)
Match join keys on true primary/foreign pairs
Validate row count after every join

Common mistakes

Joining many-to-many without pre-aggregation
Using DISTINCT to hide a bad join
Forgetting LEFT JOIN when keeping base rows

High-level strategy

Start with INNER JOIN to verify key matches
Switch to LEFT JOIN only when preserving base rows matters
Use COUNT(*) and COUNT(DISTINCT key) as join safety checks

Task ladder

Baseline query: get a correct first output.
Add one complication: edge case, join, filter, or ranking rule.
Re-run and refine until output is stable and explainable.

Transparent data checks

Check row count before and after each major clause.
Validate one manual sample row for correctness.
Confirm final ordering and column naming for trust.

Retention loop

Run the solution once.
Rewrite from memory with one variation.
Explain the logic in plain English in one sentence.

Logical reasoning for commands

JOIN ... ON

Why: Connects rows only when keys truly match.

Memory cue: No key match = no trusted relationship.

LEFT JOIN

Why: Keeps base table rows even when lookup data is missing.

Memory cue: Left keeps the story intact.

COUNT(DISTINCT key)

Why: Confirms joins did not multiply business entities.

Memory cue: Distinct key count should stay believable.

Concept check

Quick check: RIGHT JOIN and FULL OUTER JOIN

3 quick questions. One at a time. Instant score at the end.

Exercise 1

All customers and their order IDs (RIGHT JOIN)

Commerce Operations Intelligenceeasy

From the store sandbox, return customer id, first_name, last_name, and order_count (including 0 for customers with no orders). Use a RIGHT JOIN from orders to customers. Order by id.

Run a query to see results here.

Exercise 2

Jobs and employees (FULL OUTER JOIN)

Enterprise Workforce Analyticseasy

From the HR sandbox, return job title and employee first_name + last_name using a FULL OUTER JOIN between jobs and employees on job_id. Show NULL where no match exists. Order by title NULLS LAST, then last_name NULLS LAST.

Run a query to see results here.

Exercise 3

All departments and all doctors (FULL OUTER JOIN)

Clinical Care Performanceeasy

From the hospital sandbox, FULL OUTER JOIN departments to doctors on department_id. Return department name and doctor first_name, last_name. Order by name NULLS LAST, last_name NULLS LAST.

Run a query to see results here.

Lesson 3 of 4

0/3 solved (0%)

← Previous

Finish this lesson to unlock next.