INNER JOIN
Combines rows from two or more tables based on a related column
SELECT columns FROM table1 INNER JOIN table2 ON table1.id = table2.foreign_id;This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.
What it does
Overview
Combines rows from two or more tables based on a related column
The INNER JOIN clause creates a new result set by combining rows from two or more tables where a specific condition, known as the join predicate, is met. It is the default join type in most SQL dialects and acts as a strict filter: if a row in the first table does not have a corresponding match in the second table based on the join condition, that row is excluded from the final result. From a performance perspective, INNER JOINs are highly efficient when the columns involved in the ON clause (typically primary and foreign keys) are properly indexed, allowing the database engine to utilize optimized algorithms such as Nested Loop, Hash Join, or Merge Join. A significant edge case involves NULL values: because NULL represents an unknown value, the comparison 'NULL = NULL' evaluates to UNKNOWN (effectively false) in SQL logic, meaning rows with NULL in the join column will be omitted from the results. To maintain data integrity and performance, ensure that join columns have compatible data types to avoid implicit type casting during the execution phase.
Quick reference
Syntax
SELECT columns FROM table1 INNER JOIN table2 ON table1.id = table2.foreign_id;Inputs
Parameters
See it in practice
Examples
Basic Two-Table Join
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;order_id | customer_name ---------|-------------- 101 | Alice Smith 102 | Bob Jones
Retrieves the order ID and the associated customer name by matching the d in the orders table with the unique id in the customers table.
Joining Three Tables with Aliases
SELECT e.last_name, d.dept_name, l.city
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
INNER JOIN locations l ON d.loc_id = l.id;last_name | dept_name | city ----------|-----------|--------- Miller | Sales | New York Davis | IT | London
Demonstrates a multi-table join using table aliases (e, d, l) to improve readability and resolve column source ambiguity.
Inner Join with Aggregate Functions
SELECT d.dept_name, COUNT(e.id) AS staff_count
FROM departments d
INNER JOIN employees e ON d.id = e.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.id) > 5;dept_name | staff_count ----------|------------ Engineering| 12 Marketing | 8
Combines tables to count employees per department, only including departments that actually have employees assigned to them.
Debug faster
Common Errors
LogicError
Cause: Ambiguous column names when multiple tables share the same column header (e.g., 'id' or 'created_at').
Fix: Explicitly qualify column names using the table name or table alias.
SELECT id FROM users INNER JOIN profiles ON users.id = profiles.user_id; -- Error: Column 'id' in field list is ambiguousLogicError
Cause: Unexpected loss of data due to NULL values in the join column or missing records in the related table.
Fix: Use a LEFT JOIN if you need to keep all records from the primary table even if no match exists in the secondary table.
SELECT * FROM employees INNER JOIN projects ON employees.project_id = projects.id; -- Will hide employees not assigned to a projectRuntime support
Compatibility
Standard feature, widely supported
Source: MDN Web Docs
Common questions
Frequently Asked Questions
Combines rows from two or more tables based on a related column
table1: The first table (left side) table2: The second table to join (right side) ON condition: The condition that defines how tables are related
LogicError: Explicitly qualify column names using the table name or table alias. LogicError: Use a LEFT JOIN if you need to keep all records from the primary table even if no match exists in the secondary table.