PostgreSQL LEFT JOIN

LEFT JOIN (equivalently LEFT OUTER JOIN) is the go-to join type when you need to keep every row from the primary table regardless of whether a related row exists in the secondary table. Unmatched rows from the left table appear in the result with NULL in all right-side columns — they are never silently excluded.

Syntax

SELECT select_list
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

LEFT OUTER JOIN and LEFT JOIN are identical — the OUTER keyword is optional:

-- These produce exactly the same result
SELECT * FROM t1 LEFT JOIN      t2 ON t1.id = t2.t1_id;
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.t1_id;

Practical Example

The following schema models employees and the projects they are assigned to:

CREATE TABLE employees (
  employee_id   SERIAL PRIMARY KEY,
  full_name     VARCHAR(120) NOT NULL,
  department    VARCHAR(80)
);

CREATE TABLE project_assignments (
  assignment_id SERIAL PRIMARY KEY,
  employee_id   INT REFERENCES employees(employee_id),
  project_name  VARCHAR(120) NOT NULL,
  role          VARCHAR(80),
  start_date    DATE NOT NULL
);

INSERT INTO employees (full_name, department) VALUES
  ('Priya Anand',    'Engineering'),
  ('Marcus Chen',    'Design'),
  ('Talia Ferreiro', 'Engineering'),
  ('Obi Nwosu',      'Sales'),
  ('Ingrid Lund',    'Marketing');

INSERT INTO project_assignments (employee_id, project_name, role, start_date) VALUES
  (1, 'Platform Rewrite',  'Lead Engineer',    '2026-01-10'),
  (1, 'Data Pipeline',     'Contributor',      '2026-03-01'),
  (2, 'Brand Refresh',     'Lead Designer',    '2026-02-14'),
  (3, 'Platform Rewrite',  'Engineer',         '2026-01-10');
-- Obi (Sales) and Ingrid (Marketing) have no project assignments

All employees with their project assignments (or NULL)

SELECT
  e.full_name,
  e.department,
  pa.project_name,
  pa.role
FROM employees e
LEFT JOIN project_assignments pa ON pa.employee_id = e.employee_id
ORDER BY e.full_name, pa.project_name;

Obi and Ingrid appear in the result with NULL in project_name and role — they are not lost just because they have no matching rows in project_assignments.

Anti-join — employees with no project assigned

SELECT e.full_name, e.department
FROM employees e
LEFT JOIN project_assignments pa ON pa.employee_id = e.employee_id
WHERE pa.assignment_id IS NULL;

This returns only the employees with no project assignment — a standard anti-join pattern.

Count assignments per employee including zeros

SELECT
  e.full_name,
  COUNT(pa.assignment_id) AS assignment_count
FROM employees e
LEFT JOIN project_assignments pa ON pa.employee_id = e.employee_id
GROUP BY e.employee_id, e.full_name
ORDER BY assignment_count DESC;

COUNT(pa.assignment_id) returns 0 for employees with no assignments because COUNT ignores NULL values — a useful property specific to column-reference aggregation.

ON vs WHERE for Right-Table Filters

This is one of the most common sources of bugs in LEFT JOIN queries:

-- WRONG: WHERE converts LEFT JOIN to INNER JOIN
-- Employees with no assignment are dropped because NULL != 'Platform Rewrite'
SELECT e.full_name, pa.project_name
FROM employees e
LEFT JOIN project_assignments pa ON pa.employee_id = e.employee_id
WHERE pa.project_name = 'Platform Rewrite';

-- CORRECT: ON preserves all employees; filter applied during join
-- Employees not on 'Platform Rewrite' appear with NULL in project_name
SELECT e.full_name, pa.project_name
FROM employees e
LEFT JOIN project_assignments pa
  ON  pa.employee_id   = e.employee_id
  AND pa.project_name  = 'Platform Rewrite';

Testing with Vela

LEFT JOIN anti-join patterns are common in data quality checks — for example, finding products with no orders or employees with no assignments. Vela’s database branching lets you run these queries against a production-data branch to validate the results before building a scheduled cleanup job or adding a foreign key constraint that would block the missing rows.

Production Tips

• Index the foreign key column on the right-side table (e.g., project_assignments.employee_id) to prevent a sequential scan when joining on a large table.
• Always put right-table filter conditions in the ON clause, not WHERE, if you want to preserve left-join semantics.
• Use COALESCE(pa.project_name, 'Unassigned') to replace NULL values with a readable default in reports.
• For anti-joins, benchmark both LEFT JOIN ... WHERE IS NULL and NOT EXISTS — on large tables with good indexes, NOT EXISTS often produces a more efficient plan.
• Use EXPLAIN ANALYZE to confirm the join strategy; watch for unexpected sequential scans or Hash Join with very high estimated row counts.
• When chaining multiple LEFT JOINs, apply the most selective INNER filter earliest to reduce the working set before later joins.