Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
JOINs combine rows from two or more tables based on a related column. They’re one of the most-used features in SQL and one of the most commonly misunderstood — especially when NULLs or missing rows are involved.

​
The Tables Used in This Guide

CREATE TABLE customers (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,    -- nullable: orders without a customer
    amount      DECIMAL(10,2) NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES
    (1, 1, 99.00),   -- Alice's order
    (2, 1, 45.00),   -- Alice's second order
    (3, 2, 120.00),  -- Bob's order
    (4, NULL, 15.00); -- order with no customer

​
JOIN Types at a Glance

TypeReturnsUse when
INNER JOINOnly rows with a match in both tablesYou want results that exist on both sides
LEFT JOINAll rows from the left table; NULL for unmatched right rowsYou want all left-side rows regardless of whether a match exists
RIGHT JOINAll rows from the right table; NULL for unmatched left rowsSame result as LEFT JOIN with tables swapped — most developers rewrite it that way
CROSS JOINEvery combination of left and right rows (Cartesian product)Generating all possible pairs; no ON condition

​
INNER JOIN — Only Matching Rows

INNER JOIN (or just JOIN) returns rows where the join condition matches in both tables. Rows with no match in either table are excluded. 
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

name  | amount
------+--------
Alice | 99.00
Alice | 45.00
Bob   | 120.00
Carol has no orders — she’s excluded. The order with customer_id = NULL is also excluded because NULL doesn’t match any value. Only rows with a match on both sides appear. For a full explanation of how MySQL handles NULL comparisons, see NULL in MySQL.

​
LEFT JOIN — All Rows from the Left Table

LEFT JOIN returns all rows from the left table, plus any matching rows from the right table. When there’s no match, the right-side columns are NULL. 
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

name  | amount
------+--------
Alice | 99.00
Alice | 45.00
Bob   | 120.00
Carol | NULL
Carol appears now with NULL for amount. This is the right JOIN to use when you want all rows from the primary table, whether or not related rows exist. Finding rows with no match — filter on a NULL right-side column: 
-- Customers who have never placed an order
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

name
------
Carol

​
RIGHT JOIN — All Rows from the Right Table

RIGHT JOIN is the mirror of LEFT JOIN: all rows from the right table, NULLs for unmatched left-side columns. In practice, most developers rewrite RIGHT JOIN as a LEFT JOIN with the tables swapped — it’s easier to read. 
-- These are equivalent
SELECT c.name, o.amount
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;

SELECT c.name, o.amount
FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;
Both return all orders, including the one with no customer: 
name  | amount
------+--------
Alice | 99.00
Alice | 45.00
Bob   | 120.00
NULL  | 15.00

​
CROSS JOIN — Every Combination

CROSS JOIN produces the Cartesian product: every row from the left table paired with every row from the right table. With 3 customers and 4 orders, you get 12 rows. There’s no ON condition. 
SELECT c.name, o.amount
FROM customers c
CROSS JOIN orders o;
-- Returns 3 × 4 = 12 rows
Useful for generating combinations (e.g., all product-size pairings), not for querying related data.

​
JOIN Performance

JOINs on large tables can be slow if the join columns aren’t indexed. 
-- Without an index on orders.customer_id, MySQL scans all orders for each customer
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

-- Add the index
CREATE INDEX idx_orders_customer ON orders (customer_id);
Always index the foreign key column on the “many” side of a one-to-many relationship. EXPLAIN shows the join order and which indexes are used — look for type: ALL on any joined table as a sign that an index is missing.

​
Frequently Asked Questions

​
What’s the difference between JOIN and INNER JOIN?

Nothing. They’re the same. INNER JOIN is the explicit form; JOIN is shorthand. Both return only matching rows.

​
Why does adding a WHERE clause to a LEFT JOIN turn it into an INNER JOIN?

When you filter on a column from the right table in the WHERE clause, rows where that column is NULL (the unmatched rows) are excluded. Put the condition in the ON clause instead if you want to keep unmatched rows: 
-- This excludes customers with no orders — behaves like INNER JOIN
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 50;

-- This keeps customers with no orders
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 50;

​
Does MySQL support FULL OUTER JOIN?

No — MySQL doesn’t have FULL OUTER JOIN. Emulate it with a LEFT JOIN and RIGHT JOIN combined with UNION: 
SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.amount FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;
UNION deduplicates the result. If your data can have legitimately duplicate rows and you want to preserve them, use UNION ALL — but be aware it will also include rows that appear in both halves.

​
Troubleshooting

ProblemSolution
LEFT JOIN returns fewer rows than expectedA WHERE condition on the right table is filtering out NULL rows — move the condition to ON
JOIN on large tables is slowAdd an index on the join column of the larger table
Unexpected duplicate rowsThe join is producing multiple matches — use GROUP BY or check whether the relationship is truly one-to-one
RIGHT JOIN is confusingRewrite as LEFT JOIN with tables swapped — same result, easier to read