SQLite LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. When no match exists in the right table, SQLite fills the right table's columns with NULL. This makes LEFT JOIN ideal for finding unmatched records — customers with no orders, employees with no department, or products with no sales.

LEFT JOIN vs INNER JOIN Diagram

Left Table (customers)    Right Table (orders)
──────────────────────    ──────────────────────
id │ name                 id │ cust_id │ product
───┼──────                ───┼─────────┼────────
1  │ Alice                1  │ 1       │ Laptop
2  │ Bob                  2  │ 1       │ Phone
3  │ Carol                3  │ 3       │ Tablet
4  │ Dan   (no orders)

INNER JOIN result:              LEFT JOIN result:
──────────────────────          ──────────────────────────────
name  │ product                 name  │ product
──────┼─────────                ──────┼──────────────
Alice │ Laptop                  Alice │ Laptop
Alice │ Phone                   Alice │ Phone
Carol │ Tablet                  Carol │ Tablet
                                Dan   │ NULL   ← included!
                                              no match = NULL

LEFT JOIN Syntax

SELECT columns
FROM   left_table
LEFT JOIN right_table ON left_table.key = right_table.key;

-- "LEFT OUTER JOIN" is the same thing — OUTER is optional

Full Working Example

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT
);
INSERT INTO customers VALUES (1,'Alice'),(2,'Bob'),(3,'Carol'),(4,'Dan');

CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  cust_id INTEGER,
  product TEXT,
  amount  REAL
);
INSERT INTO orders VALUES
  (1,1,'Laptop',1200),(2,1,'Phone',800),(3,3,'Tablet',600);

-- LEFT JOIN: show all customers, with order info if available
SELECT c.name, o.product, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
ORDER BY c.name;

name  │ product │ amount
──────┼─────────┼────────
Alice │ Laptop  │ 1200
Alice │ Phone   │ 800
Bob   │ NULL    │ NULL    ← Bob has no orders
Carol │ Tablet  │ 600
Dan   │ NULL    │ NULL    ← Dan has no orders

Finding Records with No Match

LEFT JOIN combined with WHERE right.col IS NULL finds rows in the left table that have no match in the right table. This is the classic "anti-join" pattern.

-- Customers who have never placed an order
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
WHERE o.id IS NULL;

name
──────
Bob
Dan

LEFT JOIN with Aggregation

-- Order count and total per customer (include zero-order customers)
SELECT c.name,
       COUNT(o.id)    AS order_count,
       COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
GROUP BY c.id
ORDER BY total_spent DESC;

name  │ order_count │ total_spent
──────┼─────────────┼────────────
Alice │ 2           │ 2000
Carol │ 1           │ 600
Bob   │ 0           │ 0
Dan   │ 0           │ 0

Note: COALESCE converts NULL sums to 0 for display.

Multiple LEFT JOINs

-- All customers with their orders and payment status
SELECT c.name, o.product, p.status
FROM customers c
LEFT JOIN orders   o ON c.id = o.cust_id
LEFT JOIN payments p ON o.id = p.order_id;

LEFT JOIN vs RIGHT JOIN in SQLite

SQLite historically only supported LEFT JOIN. RIGHT JOIN (keep all rows from the right table) was added in SQLite 3.39.0. You can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order.

-- RIGHT JOIN (SQLite 3.39+):
SELECT c.name, o.product
FROM orders o
RIGHT JOIN customers c ON o.cust_id = c.id;

-- Equivalent LEFT JOIN (works in all SQLite versions):
SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id;

When to Use LEFT JOIN

USE LEFT JOIN WHEN:
──────────────────────────────────────────────────────
✔ You need all rows from the left table, matched or not
✔ Finding records with no related records (anti-join)
✔ Counting items including those with zero matches
✔ Reporting must show all entities even without activity
✔ Combining optional one-to-many relationships

Key Takeaways

• LEFT JOIN returns all left-table rows; right-table columns are NULL when no match exists
• INNER JOIN excludes unmatched rows; LEFT JOIN keeps them
• Use WHERE right_table.col IS NULL to find left-table rows with no match
• Use COALESCE to replace NULL sums with 0 in aggregation queries
• RIGHT JOIN can always be rewritten as a LEFT JOIN with swapped table order