QuerydrillINNER JOIN
INNER JOIN combines two tables and keeps only the rows where the ON condition matches on both sides: SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id returns each order with its customer name. Orders without a customer and customers without orders are left out. JOIN alone means INNER JOIN.
Syntax
SELECT columns FROM table1 [AS] t1 INNER JOIN table2 [AS] t2 ON t1.key = t2.key;| Part | What it is |
|---|---|
ON condition | How rows match, usually a foreign key equals a primary key |
table aliases | Short names (o, c) used to prefix columns from each table |
Examples
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;Result: id | amount | name 1 | 120.0 | Maria 2 | 35.5 | Luis
SELECT p.name, SUM(oi.qty * oi.price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.name
ORDER BY revenue DESC;Result: Revenue per product, biggest first
SELECT c.name, p.name AS product, oi.qty
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;Result: One row per order line with customer and product names
Chain as many JOIN ... ON pairs as you need; each one adds a table.
Common mistakes
- An ambiguous column error (name, id) happens when both tables have that column; prefix it with the table alias.
- Rows multiply when the join key is not unique on one side (a customer with 3 orders appears 3 times); aggregate first or join on the correct key.
- Forgetting ON (or writing a cross join by listing tables with a comma and no WHERE) multiplies every row with every row.
Practice INNER JOIN
These drills check your answer on live data, free, no account needed.
- Orders with customer namesReturn one row per order: the order id, the customer name and the amount.
- Revenue per productUsing order lines, return each product name with its revenue: quantity times price, summed over all its order lines. Products never sold do not appear.
Questions
What is the difference between JOIN and INNER JOIN?
None. JOIN defaults to INNER JOIN in SQLite, PostgreSQL and every major database.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only matching rows from both tables. LEFT JOIN keeps every row of the left table and fills the right side with NULL when there is no match.
Related
- LEFT JOINLEFT JOIN returns every row of the first table and matching rows from the second, or NULL. Find rows without a match, count with zeros, avoid the WHERE trap.
- Self joinA self join joins a table to itself using two aliases. Typical uses: employee and manager in the same table, comparing rows in pairs, rows that share a value.
- FULL OUTER JOINFULL OUTER JOIN returns all rows from both tables, matched where possible and NULL elsewhere. Syntax, rows present on only one side, and the UNION workaround.
- Aliases (AS)An alias gives a column or table a temporary name: SELECT price * qty AS total, FROM orders o. Where aliases are allowed (ORDER BY yes, WHERE no) and quoting.