SQL JOIN Examples
SQL JOINs combine rows from two or more tables based on a related column. Understanding the different join types — INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF — is fundamental to writing effective database queries. Each type controls which rows are included when records don't match.
SQLJoinsDatabaseQuery
Core Join Types
SELECT u.name, o.order_date, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
INNER JOIN returns only rows where the join condition matches in both tables. Users with no orders are excluded, and orders with no matching user are excluded. This is the most common join type for retrieving related records.
SELECT u.name, o.order_date, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
LEFT JOIN returns all rows from the left table (users) plus matching rows from the right table (orders). Users without orders appear with NULL for order columns. Use LEFT JOIN to find records that may or may not have related data.
SELECT u.name, o.order_date, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
RIGHT JOIN returns all rows from the right table (orders) plus matching rows from the left (users). Orders without a matching user appear with NULL for user columns. Less common than LEFT JOIN — you can usually rewrite by swapping the table order.
SELECT u.name, o.order_date
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
FULL OUTER JOIN returns all rows from both tables. Unmatched rows from either side appear with NULLs for the other table's columns. Useful for finding all discrepancies between two datasets — records that exist in one but not the other.
Advanced Patterns
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
Filter a LEFT JOIN for NULL on the right side to find records with no match. This returns all users who have never placed an order. This pattern (LEFT JOIN + WHERE IS NULL) is more efficient than NOT IN subqueries on large tables.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
A SELF JOIN joins a table to itself using different aliases. This example retrieves each employee alongside their manager's name, where both exist in the same employees table. Essential for hierarchical data like org charts and category trees.
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;
Combine a LEFT JOIN with GROUP BY to count or sum related records per parent row. Using LEFT JOIN ensures users with zero orders appear with count 0 rather than being excluded, giving a complete picture of all users.
SELECT u.name, p.name AS product, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;
Chain multiple JOINs to traverse several tables at once. Each JOIN adds another table to the result set. Ensure proper indexes exist on join columns (foreign keys) to avoid slow full-table scans on large datasets.
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o USING (user_id);
When both tables have a column with the same name, USING (column_name) is cleaner than ON table1.col = table2.col. The column appears only once in SELECT * results. Supported in PostgreSQL, MySQL, and SQLite.
How to Use
- Choose INNER JOIN when you only want rows with matches in both tables.
- Choose LEFT JOIN when you want all left-table rows regardless of matches.
- Add WHERE right_table.id IS NULL after LEFT JOIN to find unmatched records.
- Always alias tables with short names (u, o, p) when joining multiple tables.
Frequently Asked Questions
What is the most commonly used join type?
INNER JOIN and LEFT JOIN cover the vast majority of real-world use cases. INNER JOIN is the default when you need only matching rows. LEFT JOIN is used when you need all records from the primary table regardless of whether related data exists.
How do JOINs affect query performance?
JOINs on columns without indexes cause full table scans and can be very slow on large tables. Always create indexes on foreign key columns used in JOIN conditions. Use EXPLAIN or EXPLAIN ANALYZE to see the query plan and identify missing indexes.
What is a Cartesian product and when does it happen?
A Cartesian product (CROSS JOIN) returns every combination of rows from both tables. It happens accidentally if you forget the ON clause in a JOIN. With tables of 1000 and 500 rows, the result has 500,000 rows. Always double-check that your JOIN has a proper ON condition.
Related Tools