Joins Deep Dive

Lesson 0011 — Combining Tables with ON, Aliases & Join Types — ~20 min read

One table is rarely the whole story. Real data is relational — customers ↔ orders, employees ↔ managers — joined on keys. ON defines how rows match; WHERE defines which joined rows survive.

Most datasets are relational — information spread across tables connected by common fields. To analyze behavior, sales, or activity you must combine tables. This chapter covers JOINS with ON and aliases.

CREATE TABLE customers (
  customer_id     INT PRIMARY KEY,
  name            VARCHAR(50) NOT NULL,
  email           VARCHAR(120),
  city            VARCHAR(60),
  signup_at_utc   DATETIME
);
CREATE TABLE orders (
  order_id        INT PRIMARY KEY, customer_id INT,
  order_code      VARCHAR(10) NOT NULL,
  status          VARCHAR(20) NOT NULL,
  total_amount    DECIMAL(10,2) NOT NULL,
  placed_at_utc   DATETIME NOT NULL,
  INDEX idx_orders_customer_id (customer_id)
);
INSERT INTO customers (customer_id, name, email, city, signup_at_utc) VALUES
(101, 'Aisha',  'aisha@demo.com',  'Delhi',     '2026-01-01 10:00:00'),
(102, 'Rohan',  'rohan@demo.com',  'Mumbai',    '2026-01-02 11:00:00'),
(103, 'Meera',  'meera@demo.com',  NULL,        '2026-01-03 12:00:00'),
(104, 'Arjun',  'arjun@demo.com',  'Delhi',     '2026-01-04 13:00:00'),
(106, 'Neha',   'neha@demo.com',   'Pune',      '2026-01-05 09:30:00'),
(107, 'Ishaan', 'ishaan@demo.com', 'Bengaluru', '2026-01-06 18:10:00'),
(108, 'Riya',   NULL,              'Delhi',     '2026-01-07 07:45:00'),
(109, 'Aisha',  'aisha2@demo.com', 'Jaipur',    '2026-01-08 20:00:00');
INSERT INTO orders (order_id, customer_id, order_code, status, total_amount, placed_at_utc) VALUES
(1, 101, 'A', 'PAID',      499.00, '2026-01-10 10:00:00'),
(2, 102, 'B', 'PAID',      299.00, '2026-01-10 11:00:00'),
(3, 105, 'C', 'PAID',      199.00, '2026-01-10 12:00:00'),
(4, 101, 'D', 'CANCELLED', 999.00, '2026-01-11 09:00:00'),
(5, 106, 'E', 'PAID',      799.00, '2026-01-11 10:30:00'),
(6, 106, 'F', 'PENDING',   149.00, '2026-01-12 08:20:00'),
(7, NULL,'G', 'PAID',      249.00, '2026-01-12 09:10:00'),
(8, 999, 'H', 'PAID',      129.00, '2026-01-12 15:40:00');
CREATE TABLE customers (
  customer_id     INT PRIMARY KEY,
  name            VARCHAR(50) NOT NULL,
  email           VARCHAR(120),
  city            VARCHAR(60),
  signup_at_utc   TIMESTAMP
);
CREATE TABLE orders (
  order_id        INT PRIMARY KEY, customer_id INT,
  order_code      VARCHAR(10) NOT NULL,
  status          VARCHAR(20) NOT NULL,
  total_amount    DECIMAL(10,2) NOT NULL,
  placed_at_utc   TIMESTAMP NOT NULL
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- same INSERTs (DATETIME → TIMESTAMP)

RIGHT JOIN — Keep All Right Rows

RIGHT JOIN returns all rows from the right table (orders), plus matching left rows (customers). No match → left columns become NULL.

RIGHT JOIN diagram

Scenario 1: Every order, attach customer name if known

SELECT o.order_id, o.order_code, o.status, o.total_amount, o.customer_id,
       c.name AS customer_name
FROM customers AS c
RIGHT JOIN orders AS o ON c.customer_id = o.customer_id
ORDER BY o.order_id;
SELECT o.order_id, o.order_code, o.status, o.total_amount, o.customer_id,
       c.name AS customer_name
FROM customers AS c
RIGHT JOIN orders AS o ON c.customer_id = o.customer_id
ORDER BY o.order_id;
order_idorder_codestatustotal_amountcustomer_idcustomer_name
1APAID499.00101Aisha
2BPAID299.00102Rohan
3CPAID199.00105NULL
4DCANCELLED999.00101Aisha
5EPAID799.00106Neha
6FPENDING149.00106Neha
7GPAID249.00NULLNULL
8HPAID129.00999NULL

Orders 3/8 have unknown customers, order 7 has NULL — all still appear because orders is preserved.

Scenario 2: PAID orders with customer city

SELECT o.order_id, o.order_code, o.status, o.total_amount,
       c.name AS customer_name, c.city
FROM customers AS c
RIGHT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.status = 'PAID'
ORDER BY o.order_id;
SELECT o.order_id, o.order_code, o.status, o.total_amount,
       c.name AS customer_name, c.city
FROM customers AS c
RIGHT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.status = 'PAID'
ORDER BY o.order_id;
order_idorder_codestatustotal_amountcustomer_namecity
1APAID499.00AishaDelhi
2BPAID299.00RohanMumbai
3CPAID199.00NULLNULL
5EPAID799.00NehaPune
7GPAID249.00NULLNULL
8HPAID129.00NULLNULL

WHERE o.status='PAID' filters after the join but preserves unmatched orders (still PAID).

Scenario 3: City = Delhi in ON vs WHERE

SELECT o.order_code, c.customer_id, c.city
FROM customers AS c
RIGHT JOIN orders AS o ON c.customer_id = o.customer_id
                    AND c.city = 'Delhi';
SELECT o.order_code, c.customer_id, c.city
FROM customers AS c
RIGHT JOIN orders AS o ON c.customer_id = o.customer_id
                    AND c.city = 'Delhi';
order_codecustomer_idcity
A101Delhi
BNULLNULL
CNULLNULL
D101Delhi
ENULLNULL
FNULLNULL
GNULLNULL
HNULLNULL

c.city='Delhi' in ON means match only if customer is Delhi — otherwise left columns become NULL, but right rows still survive.

ON vs WHERE

AspectON (Join Condition)WHERE (Filter Condition)
WhenDuring matchingAfter join result
PurposeDefines how rows matchDefines which joined rows remain
Best forKeeping all left/right rows but controlling matchesFiltering final output

LEFT JOIN — Keep All Left Rows

LEFT JOIN keeps all left table (customers) rows; no right match → right columns NULL.

LEFT JOIN diagram

Scenario 1: Every customer, with order if any

SELECT c.customer_id, c.name, o.order_id, o.order_code
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;
SELECT c.customer_id, c.name, o.order_id, o.order_code
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;
customer_idnameorder_idorder_code
101Aisha1A
101Aisha4D
102Rohan2B
103MeeraNULLNULL
104ArjunNULLNULL
106Neha5E
106Neha6F
107IshaanNULLNULL
108RiyaNULLNULL
109AishaNULLNULL

Scenario 2: Customers who never ordered

SELECT c.customer_id, c.name, o.order_id, o.order_code
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
SELECT c.customer_id, c.name, o.order_id, o.order_code
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
customer_idnameorder_idorder_code
103MeeraNULLNULL
104ArjunNULLNULL
107IshaanNULLNULL
108RiyaNULLNULL
109AishaNULLNULL

After LEFT JOIN, unmatched customers have o.order_id IS NULL — filter to find them.

INNER JOIN — Only Matching Rows

INNER JOIN keeps only rows where both sides match.

INNER JOIN diagram

Scenario 1: Orders per customer

SELECT c.customer_id, c.name, COUNT(*) AS orders_count
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
SELECT c.customer_id, c.name, COUNT(*) AS orders_count
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
customer_idnameorders_count
101Aisha2
102Rohan1
106Neha2

Scenario 2: Revenue per customer

SELECT c.customer_id, c.name, SUM(o.total_amount) AS revenue
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
SELECT c.customer_id, c.name, SUM(o.total_amount) AS revenue
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
customer_idnamerevenue
101Aisha1498.00
102Rohan299.00
106Neha948.00

Unmatched orders (105, 999, NULL) are ignored.

FULL OUTER JOIN — All Rows from Both

student_idname
1ASHA
2RAVI
3NEHA
NULLRAJ
student_idamount
1500
2700
4800
-- MySQL: emulate with LEFT + UNION + RIGHT
SELECT s.student_id, s.name, p.amount
FROM students AS s LEFT JOIN payments AS p ON p.student_id = s.student_id
UNION
SELECT p.student_id, s.name, p.amount
FROM students AS s RIGHT JOIN payments AS p ON p.student_id = s.student_id
WHERE s.student_id IS NULL;
SELECT COALESCE(s.student_id, p.student_id) AS student_id, s.name, p.amount
FROM students AS s
FULL OUTER JOIN payments AS p ON p.student_id = s.student_id
ORDER BY student_id;
student_idnameamount
1ASHA500
2RAVI700
3NEHANULL
NULLRAJNULL
4NULL800

CROSS JOIN — Cartesian Product

Every row from left pairs with every row from right → m × n rows. No ON needed. Useful for combinations (customer × coupon, dates × products).

SELECT COUNT(*) FROM customers AS c CROSS JOIN orders AS o;
SELECT COUNT(*) FROM customers AS c CROSS JOIN orders AS o;
COUNT(*)
64

8 customers × 8 orders = 64. Grows fast — use carefully.

IMPLICIT JOIN — Old Style (Comma + WHERE)

SELECT c.customer_id, c.name, o.order_code, o.status
FROM customers AS c, orders AS o
WHERE c.customer_id = o.customer_id;
SELECT c.customer_id, c.name, o.order_code, o.status
FROM customers AS c, orders AS o
WHERE c.customer_id = o.customer_id;

Comma forms Cartesian product, then WHERE filters to matches — equivalent to INNER JOIN … ON. Without WHERE, it becomes a CROSS JOIN.

customer_idnameorder_codestatus
101AishaAPAID
102RohanBPAID
101AishaDCANCELLED
106NehaEPAID
106NehaFPENDING

SELF JOIN — Table Joins Itself

emp_idemp_namemanager_id
1KritiNULL
2Siddhant1
3Anamika1
4Utkarsh2
5Ronit2
6Shivi3

Scenario 1: Employee + Manager Name

SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;
employeemanager
SiddhantKriti
AnamikaKriti
UtkarshSiddhant
RonitSiddhant
ShiviAnamika

Kriti excluded — manager_id IS NULL.

Scenario 2: Direct Reports per Manager

SELECT m.emp_name AS manager, COUNT(*) AS direct_reports
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.emp_id
GROUP BY m.emp_name
ORDER BY m.emp_name;
SELECT m.emp_name AS manager, COUNT(*) AS direct_reports
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.emp_id
GROUP BY m.emp_name
ORDER BY m.emp_name;
managerdirect_reports
Anamika1
Kriti2
Siddhant2

NATURAL JOIN — Automatic Matching

Matches all columns with the same name in both tables — no ON needed. Risky: adding a same-named column later silently changes the join.

customer_idtier
101GOLD
102SILVER
104BRONZE
106GOLD
201PINNACLE
SELECT c.customer_id, cl.tier
FROM customers AS c
NATURAL JOIN customer_loyalty AS cl
ORDER BY c.customer_id;
SELECT c.customer_id, cl.tier
FROM customers AS c
NATURAL JOIN customer_loyalty AS cl
ORDER BY c.customer_id;
customer_idtier
101GOLD
102SILVER
104BRONZE
106GOLD

ON vs WHERE in an OUTER JOIN — which is correct?

Notes

Join types :-

RIGHT JOIN → all orders, NULL where no customer; WHERE o.status='PAID' after join; AND c.city='Delhi' in ON → NULL fill not filter

LEFT JOIN → all customers; WHERE o.order_id IS NULL → never ordered (Meera, Arjun, Ishaan, Riya, Aisha Jaipur)

INNER JOIN → only matches; COUNT/SUM per customer → 101:2/1498, 102:1/299, 106:2/948

FULL OUTER → all from both; MySQL: LEFT UNION RIGHT; pgsql: FULL OUTER JOIN + COALESCE

CROSS JOIN → 8×8=64, no ON; IMPLICIT (comma + WHERE) = INNER, forget WHERE → CROSS

SELF JOIN → e JOIN m ON e.manager_id=m.emp_id; NATURAL JOIN → auto on same-named cols (risky)

ON vs WHERE :-

ON = during matching (preserves outer); WHERE = after join (filters result)

Primary sources: MySQL JOIN · Postgres Joins. Ask me anything that's unclear.

← Prev: Merging Query Results

Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this applies to a specific query. Review the SQL glossary or syntax quick reference for a compressed overview.