SQL LEFT JOIN

Learn how to keep all records from the left table while retrieving matching data from another table using SQL LEFT JOIN.

What is LEFT JOIN?

The LEFT JOIN statement combines rows from two tables and returns all records from the left table. When a matching record exists in the right table, its data is included. If there is no match, the right-side columns contain NULL.

Basic LEFT JOIN Syntax

The ON clause defines the relationship between the two tables.

CREATE TABLE libraries (
  library_id INTEGER,
  library_name TEXT
);

CREATE TABLE memberships (
  member_id INTEGER,
  library_id INTEGER,
  member_name TEXT
);

INSERT INTO libraries VALUES
  (1, 'Central Library'),
  (2, 'River Library'),
  (3, 'Hill Library');

INSERT INTO memberships VALUES
  (101, 1, 'Aarav'),
  (102, 2, 'Meera');

SELECT
  l.library_name,
  m.member_name
FROM libraries AS l
LEFT JOIN memberships AS m
  ON l.library_id = m.library_id
ORDER BY l.library_id;

LEFT JOIN with Products

LEFT JOIN is useful when you want to display every category even when some categories do not contain any products.

CREATE TABLE categories (
  category_id INTEGER,
  category_name TEXT
);

CREATE TABLE products (
  product_id INTEGER,
  category_id INTEGER,
  product_name TEXT
);

INSERT INTO categories VALUES
  (1, 'Cameras'),
  (2, 'Audio'),
  (3, 'Lighting'),
  (4, 'Accessories');

INSERT INTO products VALUES
  (101, 1, 'Action Camera'),
  (102, 1, 'Studio Camera'),
  (103, 2, 'Wireless Speaker');

SELECT
  c.category_name,
  p.product_name
FROM categories AS c
LEFT JOIN products AS p
  ON c.category_id = p.category_id
ORDER BY c.category_id;

Find Customers Without Orders

A common use of LEFT JOIN is finding records that do not have a corresponding record in another table.

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER,
  product TEXT
);

INSERT INTO customers VALUES
  (1, 'Riya'),
  (2, 'Kabir'),
  (3, 'Tanya'),
  (4, 'Mohit');

INSERT INTO orders VALUES
  (501, 1, 'Backpack'),
  (502, 3, 'Headphones');

SELECT
  c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

LEFT JOIN with Employee Details

LEFT JOIN can show every employee along with their assigned project, including employees who currently have no project.

CREATE TABLE employees (
  employee_id INTEGER,
  employee_name TEXT,
  department TEXT
);

CREATE TABLE projects (
  project_id INTEGER,
  employee_id INTEGER,
  project_name TEXT
);

INSERT INTO employees VALUES
  (1, 'Ishita', 'Design'),
  (2, 'Rahul', 'Engineering'),
  (3, 'Neha', 'Testing'),
  (4, 'Varun', 'Support');

INSERT INTO projects VALUES
  (201, 1, 'Mobile Redesign'),
  (202, 2, 'Smart Sensor'),
  (203, 1, 'Website Refresh');

SELECT
  e.employee_name,
  e.department,
  p.project_name
FROM employees AS e
LEFT JOIN projects AS p
  ON e.employee_id = p.employee_id
ORDER BY e.employee_id;

LEFT JOIN with COUNT()

LEFT JOIN combined with COUNT() can show the number of related records for every row in the left table.

CREATE TABLE instructors (
  instructor_id INTEGER,
  instructor_name TEXT
);

CREATE TABLE workshops (
  workshop_id INTEGER,
  instructor_id INTEGER,
  workshop_name TEXT
);

INSERT INTO instructors VALUES
  (1, 'Nikhil'),
  (2, 'Simran'),
  (3, 'Aditya'),
  (4, 'Pooja');

INSERT INTO workshops VALUES
  (101, 1, 'Photography Basics'),
  (102, 1, 'Portrait Lighting'),
  (103, 3, 'Digital Drawing');

SELECT
  i.instructor_name,
  COUNT(w.workshop_id) AS workshop_count
FROM instructors AS i
LEFT JOIN workshops AS w
  ON i.instructor_id = w.instructor_id
GROUP BY i.instructor_id, i.instructor_name
ORDER BY workshop_count DESC, i.instructor_name;

LEFT JOIN with SUM()

You can use SUM() with LEFT JOIN to calculate totals while still keeping left-table records that have no related transactions.

CREATE TABLE sales_regions (
  region_id INTEGER,
  region_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  region_id INTEGER,
  amount INTEGER
);

INSERT INTO sales_regions VALUES
  (1, 'North'),
  (2, 'South'),
  (3, 'East'),
  (4, 'West');

INSERT INTO sales VALUES
  (101, 1, 1200),
  (102, 1, 800),
  (103, 2, 1500),
  (104, 3, 600);

SELECT
  r.region_name,
  COALESCE(SUM(s.amount), 0) AS total_sales
FROM sales_regions AS r
LEFT JOIN sales AS s
  ON r.region_id = s.region_id
GROUP BY r.region_id, r.region_name
ORDER BY r.region_id;

LEFT JOIN with a Date

SQLite stores dates commonly as text in formats such as YYYY-MM-DD. LEFT JOIN can be used to connect schedules with their assigned events.

CREATE TABLE rooms (
  room_id INTEGER,
  room_name TEXT
);

CREATE TABLE bookings (
  booking_id INTEGER,
  room_id INTEGER,
  booking_date TEXT
);

INSERT INTO rooms VALUES
  (1, 'Conference A'),
  (2, 'Conference B'),
  (3, 'Training Room'),
  (4, 'Meeting Room');

INSERT INTO bookings VALUES
  (101, 1, '2026-08-25'),
  (102, 3, '2026-08-26');

SELECT
  r.room_name,
  b.booking_date
FROM rooms AS r
LEFT JOIN bookings AS b
  ON r.room_id = b.room_id
ORDER BY r.room_id;

LEFT JOIN with Multiple Conditions

You can use more than one condition in the ON clause of a LEFT JOIN. Both conditions must be satisfied for the right table record to be matched.

CREATE TABLE stores (
  store_id INTEGER,
  store_name TEXT
);

CREATE TABLE inventory (
  item_id INTEGER,
  store_id INTEGER,
  item_name TEXT,
  quantity INTEGER
);

INSERT INTO stores VALUES
  (1, 'Downtown Store'),
  (2, 'Airport Store'),
  (3, 'Harbor Store'),
  (4, 'Garden Store');

INSERT INTO inventory VALUES
  (101, 1, 'Camera', 15),
  (102, 1, 'Tripod', 4),
  (103, 2, 'Headphones', 12),
  (104, 2, 'Microphone', 3),
  (105, 3, 'Speaker', 20);

SELECT
  s.store_name,
  i.item_name,
  i.quantity
FROM stores AS s
LEFT JOIN inventory AS i
  ON s.store_id = i.store_id
  AND i.quantity >= 10
ORDER BY s.store_id;

LEFT JOIN with COALESCE()

When there is no matching right-side record, SQL returns NULL. The COALESCE() function can replace that NULL value with a default value.

CREATE TABLE players (
  player_id INTEGER,
  player_name TEXT
);

CREATE TABLE scores (
  score_id INTEGER,
  player_id INTEGER,
  points INTEGER
);

INSERT INTO players VALUES
  (1, 'Karan'),
  (2, 'Diya'),
  (3, 'Manav'),
  (4, 'Tia');

INSERT INTO scores VALUES
  (101, 1, 75),
  (102, 3, 92),
  (103, 1, 88);

SELECT
  p.player_name,
  COALESCE(s.points, 0) AS points
FROM players AS p
LEFT JOIN scores AS s
  ON p.player_id = s.player_id
ORDER BY p.player_id;

LEFT JOIN with Three Tables

Multiple LEFT JOIN statements can be used when information is spread across several related tables.

CREATE TABLE members (
  member_id INTEGER,
  member_name TEXT
);

CREATE TABLE subscriptions (
  subscription_id INTEGER,
  member_id INTEGER,
  plan_id INTEGER
);

CREATE TABLE plans (
  plan_id INTEGER,
  plan_name TEXT
);

INSERT INTO members VALUES
  (1, 'Aarohi'),
  (2, 'Yash'),
  (3, 'Mira'),
  (4, 'Rudra');

INSERT INTO subscriptions VALUES
  (201, 1, 10),
  (202, 2, 20),
  (203, 1, 30);

INSERT INTO plans VALUES
  (10, 'Basic'),
  (20, 'Premium'),
  (30, 'Student');

SELECT
  m.member_name,
  p.plan_name
FROM members AS m
LEFT JOIN subscriptions AS s
  ON m.member_id = s.member_id
LEFT JOIN plans AS p
  ON s.plan_id = p.plan_id
ORDER BY m.member_id;

LEFT JOIN with CASE

A CASE expression can be used with LEFT JOIN to display a custom message when a matching record exists or does not exist.

CREATE TABLE devices (
  device_id INTEGER,
  device_name TEXT
);

CREATE TABLE repairs (
  repair_id INTEGER,
  device_id INTEGER,
  repair_status TEXT
);

INSERT INTO devices VALUES
  (1, 'Printer'),
  (2, 'Scanner'),
  (3, 'Projector'),
  (4, 'Router');

INSERT INTO repairs VALUES
  (101, 1, 'Completed'),
  (102, 3, 'Pending');

SELECT
  d.device_name,
  CASE
    WHEN r.repair_id IS NULL THEN 'No Repair'
    ELSE r.repair_status
  END AS status
FROM devices AS d
LEFT JOIN repairs AS r
  ON d.device_id = r.device_id
ORDER BY d.device_id;

LEFT JOIN with LIMIT

The LIMIT clause can restrict how many rows are returned after a LEFT JOIN.

CREATE TABLE stations (
  station_id INTEGER,
  station_name TEXT
);

CREATE TABLE trains (
  train_id INTEGER,
  station_id INTEGER,
  train_name TEXT
);

INSERT INTO stations VALUES
  (1, 'Central Station'),
  (2, 'East Station'),
  (3, 'North Station'),
  (4, 'West Station');

INSERT INTO trains VALUES
  (101, 1, 'Morning Express'),
  (102, 2, 'City Runner'),
  (103, 3, 'Night Express');

SELECT
  s.station_name,
  t.train_name
FROM stations AS s
LEFT JOIN trains AS t
  ON s.station_id = t.station_id
ORDER BY s.station_id
LIMIT 3;

Common LEFT JOIN Uses

Use Case	Purpose	Common Technique
Find missing records	Find rows without a match	WHERE right.id IS NULL
Count related records	Count child records	COUNT()
Calculate totals	Add related values	SUM()
Display defaults	Replace NULL values	COALESCE()
Combine tables	Keep all primary records	LEFT JOIN … ON

Advantages of LEFT JOIN

Keeps every record from the left table.
Includes matching records from the right table.
Shows NULL when no matching right-side record exists.
Useful for finding missing or incomplete relationships.
Works with aggregate functions such as COUNT() and SUM().
Can be combined with WHERE, GROUP BY, ORDER BY, CASE, and other SQL features.

Best Practices

Use LEFT JOIN when every row from the left table must remain in the result.
Use IS NULL when searching for records without a match.
Use table aliases to make JOIN queries easier to read.
Use COALESCE() when NULL values should be replaced with a default value.
Place right-table matching conditions carefully in the ON clause.
Use indexes on frequently joined columns for better performance with large datasets.

The LEFT JOIN statement is especially useful when you need to preserve every record from one table while retrieving related information from another table. If no related record exists, SQL returns NULL for the right-side columns.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.