SQL LEFT JOIN

Keeps every row from the LEFT table no matter what — filling in NULL for any right-side columns that have no match.

What & Why

LEFT JOIN (also written LEFT OUTER JOIN) keeps every row from the left table, regardless of whether a match exists on the right. When there's no match, the right table's columns simply show up as NULL instead of the row disappearing.

See How It Works

BUSINESS QUESTION

Product wants feedback counts beside every feature, including features that have received no feedback.

id	name	description	team	released_at	deprecated_at
1	Activation Checklist	Guides new users through setup	growth	2024-01-20	NULL
2	CSV Export	Exports report data	platform	2024-02-14	NULL
3	Invite Nudges	Prompts workspace collaboration	growth	2024-03-05	NULL
4	Legacy Dashboard	Original reporting surface	analytics	2023-08-10	2024-06-01

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

Trace the relationship row by row1× speed

product.features

id	name
1	Activation Checklist
2	CSV Export
3	Invite Nudges
4	Legacy Dashboard

COUNT MATCHES

product.feedback

id	feature_id	rating	created_at
1	1	5	2024-01-25 12:15:00+00
2	2	4	2024-02-20 15:40:00+00
3	1	4	2024-03-18 09:05:00+00
4	3	3	2024-04-08 17:30:00+00

Result set · 1 rows

name	feedback_count
Activation Checklist	2

Activation Checklist contributes its exact feedback count.

EXAMPLE QUERY

SELECT
  f.name,
  COUNT(fe.id) AS feedback_count
FROM product.features f
LEFT JOIN product.feedback fe ON fe.feature_id = f.id
GROUP BY f.id, f.name
ORDER BY feedback_count ASC, f.name;

Now You Try

Practice this concept

Product wants feedback counts beside every feature, including features with zero feedback.

Available schema

product

Prefix tables with product.table_name.

namefeedback_count

product.features

Column	Type
id	integer
name	text
description	text
team	text
released_at	timestamp with time zone
deprecated_at	timestamp with time zone
internal_priority	integer

product.feedback

Column	Type
id	integer
user_id	integer
feature_id	integer
rating	integer
nps_score	integer
comment	text
created_at	timestamp with time zone
moderation_bucket	text

query.sql

Intermediate business practice