SQL Reference ยท Joins and Subqueries
SQL INNER JOIN
Reference for SQL INNER JOIN: combine rows from two tables where the join condition matches, with ON and USING syntax, multiple joins, NULL behavior and dialects.
INNER JOIN combines rows from two tables and keeps only the pairs that satisfy the join condition. A row with no partner on the other side is dropped from the result. Because it is the most common join, JOIN on its own means INNER JOIN.
The condition usually compares a foreign key with the primary key it references, such as orders.customer_id = customers.id. You can chain more joins to bring in further tables.
SELECT o.id, c.last_name FROM orders AS o INNER JOIN customers AS c ON c.id = o.customer_id;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT columns FROM table_a [INNER] JOIN table_b ON table_a.key = table_b.key; SELECT columns FROM table_a [INNER] JOIN table_b USING (shared_column);
| Part | Description |
|---|---|
[INNER] JOIN |
INNER is optional and the default join type. |
ON condition |
Any boolean expression; rows are paired when it is true. It can use several columns and operators other than =. |
USING (column) |
Shorthand when both tables have a column of that name; the column appears once in SELECT *. |
Returns: one row for every pair of rows (one from each table) for which the condition is true. A row that matches several partners appears several times.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Supports ON, USING, NATURAL. Every join type except LATERAL. |
| PostgreSQL, MySQL, SQL Server, Oracle | The same syntax. SQL Server has no USING. |
| Old style | FROM a, b WHERE a.id = b.a_id is an inner join in every database, but mixes the join condition with filters; prefer explicit JOIN. |
| MySQL | JOIN, INNER JOIN and CROSS JOIN are interchangeable, and ON is optional. Standard SQL requires ON for INNER JOIN. |
Usage notes
- Unmatched rows disappear. A customer with no orders is absent from an inner join of customers and orders; use
LEFT JOINto keep them. NULLnever matches. ANULLforeign key finds no partner, becauseNULL = NULLis not true.- Duplicates multiply. Joining to a table with several matches repeats the left row; aggregates over joined data can be inflated.
- Qualify column names with table aliases whenever two tables share a column name (
id), or the query is ambiguous. - Put join conditions in
ONand row filters inWHERE. For an inner join the two behave the same, but the habit matters for outer joins. - Index the join columns, especially the foreign key side, for speed.
- Order of the tables does not change the inner-join result, though the optimizer picks its own execution order.