Learn SQL  /  Joins  /  INNER JOIN

INNER JOIN

A JOIN combines rows from two or more tables based on a related column. INNER JOIN, the most common type, keeps only rows that match in both tables.

Two Related Tables

Real databases split data across multiple tables to avoid repetition. This guide uses employees and departments, linked by dept_id.

R Code
-- employees: emp_id | name  | dept_id | salary
--           1      | Rafi  | 10      | 50000
--           2      | Meera | 20      | 62000
--           3      | Zayn  | NULL    | 48000   (no department yet)

-- departments: dept_id | dept_name
--              10      | Sales
--              20      | Engineering
--              30      | HR              (no employees yet)

Basic INNER JOIN

R Code
SELECT e.name, e.salary, d.dept_name
FROM employees AS e
INNER JOIN departments AS d
  ON e.dept_id = d.dept_id;
Output
name  | salary | dept_name
Rafi  | 50000  | Sales
Meera | 62000  | Engineering

Notice Zayn (no dept_id) and HR (no employees) are both missing from the result - INNER JOIN only keeps rows that successfully match on both sides.

Table Aliases

AS e and AS d are aliases - short nicknames for each table, used to prefix column names and avoid ambiguity when both tables have a column with the same name (like dept_id here).

R Code
-- AS is actually optional for table aliases
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;   -- JOIN alone means INNER JOIN

Joining Three Tables

🚀 Advanced Example
Chain multiple JOIN clauses to pull data from more than two related tables in a single query.
R Code
-- projects: project_id | dept_id | project_name
SELECT e.name, d.dept_name, p.project_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN projects p     ON d.dept_id = p.dept_id;

Key Points

  • INNER JOIN combines two tables and keeps only rows that have a match in BOTH tables.
  • The ON clause specifies which columns link the two tables together.
  • Table aliases (e, d) shorten queries and resolve ambiguity when column names repeat across tables.
  • Rows with no match on either side are dropped entirely from an INNER JOIN result.