INNER JOIN

Use INNER JOIN to join two tables.

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;

Alternative syntax using a WHERE clause (equivalent).

SELECT column FROM table1, table2
WHERE table1.join_column = table2.join_column;

Syntax	Description
INNER JOIN table2 ON condition	Retrieves only the rows from both tables that satisfy the ON condition. Rows that do not exist in either table are excluded from the result.
JOIN table2 ON condition	A shorthand for INNER JOIN. The behavior is identical.
table_name AS alias	Assigns a short alias to a table. This makes queries more concise when working with multiple tables.

The following sample code uses these two tables.

CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    salary INT
);

INSERT INTO departments VALUES (1, 'Future Gadget Lab'), (2, 'Victor Chondria University');
INSERT INTO employees VALUES
(1, 'Okabe Rintaro', 1, 300000), (2, 'Makise Kurisu', 2, 380000),
(3, 'Shiina Mayuri', 1, 280000), (4, 'Hashida Itaru', 1, 320000);

Verify the table contents.

SELECT * FROM employees;
+----+---------------+---------------+--------+
| id | name          | department_id | salary |
+----+---------------+---------------+--------+
|  1 | Okabe Rintaro |             1 | 300000 |
|  2 | Makise Kurisu |             2 | 380000 |
|  3 | Shiina Mayuri |             1 | 280000 |
|  4 | Hashida Itaru |             1 | 320000 |
+----+---------------+---------------+--------+
4 rows in set

SELECT * FROM departments;
+----+---------------------------+
| id | department_name           |
+----+---------------------------+
|  1 | Future Gadget Lab         |
|  2 | Victor Chondria University|
+----+---------------------------+
2 rows in set

Join the employees and departments tables to retrieve the employee list with department names.

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;

+---------------+--------------------------+--------+
| name          | department_name          | salary |
+---------------+--------------------------+--------+
| Okabe Rintaro | Future Gadget Lab        | 300000 |
| Makise Kurisu | Victor Chondria University| 380000 |
| Shiina Mayuri | Future Gadget Lab        | 280000 |
| Hashida Itaru | Future Gadget Lab        | 320000 |
+---------------+--------------------------+--------+
4 rows in set

Join the employees and departments tables, and filter to employees with salary 300,000 or more.

SELECT e.name, d.department_name, e.salary
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
WHERE e.salary >= 300000
ORDER BY e.salary DESC;

+---------------+--------------------------+--------+
| name          | department_name          | salary |
+---------------+--------------------------+--------+
| Makise Kurisu | Victor Chondria University| 380000 |
| Hashida Itaru | Future Gadget Lab        | 320000 |
| Okabe Rintaro | Future Gadget Lab        | 300000 |
+---------------+--------------------------+--------+
3 rows in set

Get the highest salary per department using GROUP BY (applied example).

SELECT d.department_name, MAX(e.salary) AS max_salary
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
GROUP BY d.department_name;

+--------------------------+------------+
| department_name          | max_salary |
+--------------------------+------------+
| Future Gadget Lab        |     320000 |
| Victor Chondria University|     380000 |
+--------------------------+------------+
2 rows in set

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 = 'Future Gadget Lab';

+---------------+-------------------+
| name          | department_name   |
+---------------+-------------------+
| Okabe Rintaro | Future Gadget Lab |
| Shiina Mayuri | Future Gadget Lab |
| Hashida Itaru | Future Gadget Lab |
+---------------+-------------------+
3 rows in set

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;

+---------------+--------------------------+--------+
| name          | department_name          | salary |
+---------------+--------------------------+--------+
| Okabe Rintaro | Future Gadget Lab        | 300000 |
| Makise Kurisu | Victor Chondria University| 380000 |
| Shiina Mayuri | Future Gadget Lab        | 280000 |
| Hashida Itaru | Future Gadget Lab        | 320000 |
+---------------+--------------------------+--------+
4 rows in set

Oracle traditionally used the FROM table1, table2 WHERE condition syntax, but INNER JOIN ... ON has been available since Oracle 9i. Some prefer writing explicit JOIN syntax for readability.

The type of join determines how unmatched rows are handled.

Type	Matching rows	Left table only	Right table only	Use case
INNER JOIN	Included	Excluded	Excluded	Only rows that exist in both tables
LEFT JOIN	Included	Included (right is NULL)	Excluded	Keep all rows from the left table (e.g., users + order history)
RIGHT JOIN	Included	Excluded	Included (left is NULL)	Keep all rows from the right table (often rewritable as LEFT JOIN)
FULL JOIN	Included	Included (right is NULL)	Included (left is NULL)	Keep all rows from both tables (not supported in MySQL)

The following example shows the difference when an employee with department_id=99 (no matching department) exists.

-- INNER JOIN: returns only employees whose department exists.
SELECT e.name, d.department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;
-- The employee with department_id=99 is excluded.

-- LEFT JOIN: returns all employees; department_name is NULL if no match.
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id;
-- The employee with department_id=99 has department_name = NULL.

-- RIGHT JOIN: returns all departments; name is NULL if no employees.
SELECT e.name, d.department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id;
-- Departments with no employees have name = NULL.

The "LEFT JOIN + IS NULL" pattern for finding unmatched rows is often faster than NOT IN and is widely used in production.

-- Find employees who do not belong to any department.
SELECT e.name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
WHERE d.id IS NULL;

Adding a filter condition in ON does not remove rows from a LEFT JOIN — non-matching departments are set to NULL, but the row is still returned.

SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.id
    AND d.department_name = 'Future Gadget Lab';

NG result (filter in ON): all rows are returned, but non-matching departments are NULL.

+---------------+-------------------+
| name          | department_name   |
+---------------+-------------------+
| Okabe Rintaro | Future Gadget Lab |
| Makise Kurisu | NULL              |
| Shiina Mayuri | Future Gadget Lab |
| Hashida Itaru | Future Gadget Lab |
+---------------+-------------------+
4 rows in set

SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
WHERE d.department_name = 'Future Gadget Lab';

OK result (filter in WHERE): equivalent to INNER JOIN — rows are filtered out.

+---------------+-------------------+
| name          | department_name   |
+---------------+-------------------+
| Okabe Rintaro | Future Gadget Lab |
| Shiina Mayuri | Future Gadget Lab |
| Hashida Itaru | Future Gadget Lab |
+---------------+-------------------+
3 rows in set

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 FROM table1, table2 WHERE table1.id = table2.id syntax is equivalent to INNER JOIN ... ON, but some prefer writing explicit JOIN for readability.

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