INNER JOIN

-- Join two tables using INNER JOIN.
SELECT column FROM table1
INNER JOIN table2 ON table1.join_column = table2.join_column;

-- INNER is optional (JOIN alone works the same way).
SELECT column FROM table1
JOIN table2 ON table1.join_column = table2.join_column;

-- Legacy syntax using a WHERE clause (equivalent).
SELECT column FROM table1, table2
WHERE table1.join_column = table2.join_column;

-- Join the employees table and the departments table to get a list of employees with their department names.
SELECT e.name, d.department_name, e.salary
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

-- Join the orders table and the customers table to retrieve order history.
SELECT c.name AS customer_name, o.order_date, o.total_amount
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
ORDER BY o.order_date DESC;

-- Join three tables (employees, departments, and roles).
SELECT e.name, d.department_name, r.role_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
INNER JOIN roles AS r ON e.role_id = r.id;

-- Combine with a WHERE clause to filter results.
SELECT e.name, d.department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
WHERE d.department_name = 'Engineering';

-- Result of: SELECT e.name, d.department_name, e.salary FROM employees AS e
-- INNER JOIN departments AS d ON e.department_id = d.id;
-- +----------+------------------+--------+
-- | name     | department_name  | salary |
-- +----------+------------------+--------+
-- | Tanaka   | Engineering      | 300000 |
-- | Suzuki   | Sales            | 280000 |
-- | Yamada   | Engineering      | 320000 |
-- +----------+------------------+--------+
-- Note: Employees with a NULL department_id are not included in the result.

The INNER JOIN ... ON syntax is supported across all major databases.

-- Works in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite
SELECT e.name, d.department_name, e.salary
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

Oracle traditionally used the FROM table1, table2 WHERE condition syntax, but INNER JOIN ... ON has been available since Oracle 9i. It is now recommended to use explicit JOIN syntax.

INNER JOIN is a core feature of relational databases, used to combine data from normalized tables. You specify the join condition in the ON clause, and only rows that satisfy the condition in both tables are returned.

The legacy FROM table1, table2 WHERE table1.id = table2.id syntax is equivalent to INNER JOIN ... ON, but using explicit JOIN syntax is now preferred for clarity and readability.

If you want to include rows even when there is no matching row in the other table, use LEFT JOIN / RIGHT JOIN.