INNER JOIN
INNER JOIN returns only rows that have a match in both tables.
In this page:
Syntax
SELECT columns
FROM table1
INNER JOIN table2 ON table1.key = table2.key;INNER JOIN
JOIN ... ON pairs rows using a condition, usually a foreign key equal to a primary key. Rows without a partner in the other table are dropped.
Table aliases keep queries short, and qualifying columns with the alias removes ambiguity.
JOIN by itself means INNER JOIN.
Note:
Always qualify column names with table aliases in joins.
Example: INNER JOIN
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Bob'), (3, 'Cy');
INSERT INTO orders VALUES (10, 1, 50), (11, 1, 20), (12, 2, 75);
SELECT c.name, o.id AS order_id, o.total FROM customers c INNER JOIN orders o ON o.customer_id = c.id ORDER BY o.id;
-- Output:
-- name | order_id | total
-- Ada | 10 | 50
-- Ada | 11 | 20
-- Bob | 12 | 75Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Forgetting the ON condition and getting a cartesian product
- Ambiguous column names
- Expecting unmatched rows to appear
Chapter Summary
- INNER JOIN keeps matching rows only
- ON defines the match
- Aliases shorten queries
- JOIN means INNER JOIN
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: