Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT o.id, c.last_name
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id;

Try it

SQL Books with their authors ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Chaining three tables ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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 JOIN to keep them.
  • NULL never matches. A NULL foreign key finds no partner, because NULL = NULL is 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 ON and row filters in WHERE. 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.