ON vs USING & Join Keys

Overview

Every join needs a condition that says which rows match. SQL gives you two syntaxes for this: 1. ON — the general-purpose form. Write any boolean expression: FROM orders o JOIN customers c ON c.id = o.customer_id 2. USING — a shorthand when the join column has the SAME name in BOTH tables: FROM order_items oi JOIN orders o USING (id) -- WRONG: 'id' means different things! USING only works when the column name is identical on both sides AND means the same thing. In the shared dataset, no foreign-key column shares its name with the referenced primary key (customer_id ≠ id, order_id ≠ id), so ON is the correct choice for these tables. USING would be appropriate if the orders table had a column called 'customer_id' and the customers table also had a column called 'customer_id'. Join keys can be composite — matching on multiple columns: ON a.col1 = b.col1 AND a.col2 = b.col2 or USING (col1, col2) Mental model: ON is a free-form filter applied during the join. USING is syntactic sugar for ON when the column name is shared.

Choosing the right join-condition syntax makes queries more readable. USING eliminates redundant column names in SELECT * results (the shared column appears once, not twice). Understanding join keys also helps you identify correct foreign-key relationships and avoid accidental Cartesian products.

Where used: any multi-table query choosing between ON and USING, composite key joins in normalized schemas, code reviews debating join style

Why learn this

• Knowing when USING applies lets you write cleaner, shorter joins — and knowing when it does NOT apply prevents subtle bugs
• Understanding composite join keys prepares you for real schemas where single-column keys are not enough

Query walkthrough

SELECT c.name, o.id AS order_id, p.name AS product
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE c.name = 'Carlos'
ORDER BY o.id, p.name;

Focus: Each ON clause uses different column names (customer_id→id, order_id→id, product_id→id) — USING would not work here because the foreign key columns are named differently from the primary keys they reference.

Common mistakes

• Using USING when column names differ: USING (customer_id) only works if BOTH tables have a column named customer_id. In the shared dataset, customers has 'id' and orders has 'customer_id' — different names, so USING fails. Use ON c.id = o.customer_id instead.
• Qualifying a USING column with a table alias: After USING (product_id), the joined column belongs to neither table exclusively. Writing oi.product_id in SELECT is valid, but in the USING clause itself you must not qualify: USING (product_id), not USING (oi.product_id).
• Forgetting that USING deduplicates the column: SELECT * with ON shows the join column twice (once per table). SELECT * with USING shows it once. This can surprise you if you are counting columns or building dynamic queries.

Glossary

shorthand

A shorter and quicker way of writing a piece of code or syntax.

composite

Made up of multiple parts, like a key that uses two or more columns together to uniquely identify a row.

redundant

Information that is repeated unnecessarily and does not add any new value.

Recall questions

• When can you use USING instead of ON?
• What is the difference in SELECT * output between ON and USING?
• How do you join on a composite key with ON?

Understanding checks

What rows does this return?

1, Alice 2, Alice 3, Bob 8, Bob

US customers are Alice (id=1), Bob (id=2), and Greg (id=7). The INNER JOIN matches orders to their customers. Alice has orders 1 and 2, Bob has orders 3 and 8. Greg has no orders so he does not appear in the joined result.

A developer writes: SELECT * FROM orders o JOIN customers c USING (id); — This runs without error but returns unexpected results. Why?

USING (id) matches orders.id to customers.id — it treats the order's primary key as if it equals the customer's primary key. This is wrong: order 1 gets paired with customer 1, order 2 with customer 2, etc. — which happens to look plausible for small IDs but is semantically incorrect. The correct join is ON c.id = o.customer_id, because customer_id is the foreign key.

USING requires the column name to be the same AND to represent the same concept. Here 'id' exists in both tables but means different things (order ID vs customer ID). This is why ON is necessary when column names differ or are ambiguous.

Practice tasks

Add an extra join condition

This query joins orders to customers but includes ALL orders. Modify the ON clause to also filter for only 'paid' orders — add AND o.status = 'paid' to the ON condition (not in a WHERE clause). Keep the ordering.

Challenge

Find customers and their Furniture orders

Write a query that lists each customer name, order ID, and product name for order items in the 'Furniture' category only. Use ON for all joins. Order by customer name, then order ID.

Continue learning

Previous: INNER JOIN

Return to SQL Roadmap