MySQL 8.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- "Modern Guide to SQL JOINs", https://kb.databasedesignbook.com/joins

-- tables definition
CREATE TABLE people (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  type VARCHAR(16) NOT NULL,
  manager_id INTEGER NULL
);

CREATE INDEX ndx_manager_id ON people(manager_id);

CREATE TABLE payments (
  id INTEGER NOT NULL PRIMARY KEY,
  employee_id INTEGER NULL,
  type VARCHAR(16) NOT NULL,
  amount INTEGER NOT NULL,
  date DATE NOT NULL,
  description VARCHAR(64) NOT NULL
);

CREATE INDEX ndx_employee_id ON payments(employee_id);

Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0


-- sample data
INSERT INTO people (id, name, type, manager_id) VALUES
  (1, 'Alice',  'fulltime',  NULL),
  (2, 'Bob',    'fulltime',  1),
  (3, 'Carol',  'contractor', 2),
  (4, 'David',  'contractor', 1),
  (5, 'Eugene', 'fulltime',   2);

INSERT INTO payments (id, employee_id, type, amount, date, description) VALUES
  (1,  1,    'salary', 2000, '2025-01-25', 'Salary Jan 2025'),
  (2,  1,    'bonus',  1000, '2025-02-10', 'Bonus 2024'),
  (3,  1,    'salary', 2000, '2025-02-25', 'Salary Feb 2025'),
  (4,  1,    'salary', 2000, '2025-03-25', 'Salary Mar 2025'),
  (5,  2,    'salary', 1500, '2025-02-25', 'Salary Feb 2025'),
  (6,  2,    'bonus',   700, '2025-03-10', 'Bonus 2024Q4'),
  (7,  2,    'bonus',   300, '2025-03-15', 'Bonus Mar 2025'),
  (8,  3,    'salary', 3000, '2025-04-25', 'Salary Apr 2025'),
  (9,  3,    'salary', 3000, '2025-05-25', 'Salary May 2025'),
  (10, 4,    'bonus',  1500, '2025-04-05', 'Welcome bonus'),
  (11, NULL, 'external', 5000, '2025-06-01', 'Office rent');

Records: 5  Duplicates: 0  Warnings: 0



Records: 11  Duplicates: 0  Warnings: 0


-- LEFT JOIN, N:1 case

SELECT payments.id, payments.amount, payments.date, people.id AS person_id, people.name
FROM payments LEFT JOIN people ON payments.employee_id = people.id


id
amount
date
person_id
name


1
2000
2025-01-25
1
Alice

2
1000
2025-02-10
1
Alice

3
2000
2025-02-25
1
Alice

4
2000
2025-03-25
1
Alice

5
1500
2025-02-25
2
Bob

6
700
2025-03-10
2
Bob

7
300
2025-03-15
2
Bob

8
3000
2025-04-25
3
Carol

9
3000
2025-05-25
3
Carol

10
1500
2025-04-05
4
David

11
5000
2025-06-01
null
null


-- LEFT JOIN: 1:N case

SELECT people.id, people.name, payments.id AS payment_id, payments.amount, payments.date
FROM people LEFT JOIN payments ON people.id = payments.employee_id


id
name
payment_id
amount
date


1
Alice
1
2000
2025-01-25

1
Alice
2
1000
2025-02-10

1
Alice
3
2000
2025-02-25

1
Alice
4
2000
2025-03-25

2
Bob
5
1500
2025-02-25

2
Bob
6
700
2025-03-10

2
Bob
7
300
2025-03-15

3
Carol
8
3000
2025-04-25

3
Carol
9
3000
2025-05-25

4
David
10
1500
2025-04-05

5
Eugene
null
null
null


-- LEFT JOIN: breaking the "ID equality comparison only" guideline

SELECT payments.id, payments.amount, payments.date,
       people.id AS person_id, people.name
FROM payments LEFT JOIN people
  ON payments.employee_id = people.id AND payments.amount < 1000


id
amount
date
person_id
name


1
2000
2025-01-25
null
null

2
1000
2025-02-10
null
null

3
2000
2025-02-25
null
null

4
2000
2025-03-25
null
null

5
1500
2025-02-25
null
null

6
700
2025-03-10
2
Bob

7
300
2025-03-15
2
Bob

8
3000
2025-04-25
null
null

9
3000
2025-05-25
null
null

10
1500
2025-04-05
null
null

11
5000
2025-06-01
null
null


-- LEFT JOIN: returning to the "ID equality comparison only" guideline

SELECT payments.id, payments.amount, payments.date,
       people.id AS person_id, people.name
FROM payments LEFT JOIN people
  ON payments.employee_id = people.id
WHERE payments.amount < 1000


id
amount
date
person_id
name


6
700
2025-03-10
2
Bob

7
300
2025-03-15
2
Bob


-- LEFT JOIN: self-join example

SELECT subordinates.id, subordinates.name, managers.id, managers.name
FROM people AS subordinates LEFT JOIN people AS managers
     ON subordinates.manager_id = managers.id


id
name
id
name


1
Alice
null
null

2
Bob
1
Alice

3
Carol
2
Bob

4
David
1
Alice

5
Eugene
2
Bob


-- LEFT JOIN + GROUP BY (1:N case)

SELECT people.id, COUNT(payments.id) AS cnt, SUM(payments.amount) AS total
FROM people LEFT JOIN payments ON people.id = payments.employee_id
GROUP BY people.id


id
cnt
total


1
4
7000

2
3
2500

3
2
6000

4
1
1500

5
0
null


CREATE TABLE tickets (
  id INTEGER NOT NULL PRIMARY KEY,
  description VARCHAR(255) NOT NULL,
  assigned_to INTEGER NOT NULL
);

CREATE INDEX ndx_tickets_assgn_to ON tickets(assigned_to);

INSERT INTO tickets (id, description, assigned_to) VALUES
  (1000, 'Fix frontend bug', 3),
  (1001, 'Improve backend feature', 3),
  (1002, 'Document our database', 3);

Records: 0  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


-- chained LEFT JOIN + GROUP BY (1:N case) — incorrect results

SELECT people.id, COUNT(payments.id) AS cnt, SUM(payments.amount) AS total, COUNT(tickets.id) AS ticket_cnt
FROM people LEFT JOIN payments ON people.id = payments.employee_id
  LEFT JOIN tickets ON people.id = tickets.assigned_to
GROUP BY people.id


id
cnt
total
ticket_cnt


1
4
7000
0

2
3
2500
0

3
6
18000
6

4
1
1500
0

5
0
null
0


-- fixing counts with DISTINCT — incorrect results

SELECT people.id, COUNT(DISTINCT payments.id) AS cnt, SUM(payments.amount) AS total, COUNT(DISTINCT tickets.id) AS ticket_cnt
FROM people LEFT JOIN payments ON people.id = payments.employee_id
            LEFT JOIN tickets ON people.id = tickets.assigned_to
GROUP BY people.id


id
cnt
total
ticket_cnt


1
4
7000
0

2
3
2500
0

3
2
18000
3

4
1
1500
0

5
0
null
0


-- CTE-based approach (N:1 case)

WITH payments_agg AS (
  SELECT employee_id, COUNT(*) AS cnt, SUM(amount) AS total
  FROM payments
  GROUP BY employee_id
),

tickets_agg AS (
  SELECT assigned_to, COUNT(*) AS cnt
  FROM tickets
  GROUP BY assigned_to
)

SELECT people.id, COALESCE(payments_agg.cnt, 0) AS cnt, COALESCE(payments_agg.total, 0) AS total, COALESCE(tickets_agg.cnt, 0) AS ticket_cnt
FROM people LEFT JOIN payments_agg ON people.id = payments_agg.employee_id
            LEFT JOIN tickets_agg ON people.id = tickets_agg.assigned_to
ORDER BY people.id


id
cnt
total
ticket_cnt


1
4
7000
0

2
3
2500
0

3
2
6000
3

4
1
1500
0

5
0
0
0
id	amount	date	person_id	name
1	2000	2025-01-25	1	Alice
2	1000	2025-02-10	1	Alice
3	2000	2025-02-25	1	Alice
4	2000	2025-03-25	1	Alice
5	1500	2025-02-25	2	Bob
6	700	2025-03-10	2	Bob
7	300	2025-03-15	2	Bob
8	3000	2025-04-25	3	Carol
9	3000	2025-05-25	3	Carol
10	1500	2025-04-05	4	David
11	5000	2025-06-01	null	null