SQL INNER JOIN

Learn how to combine matching records from two or more tables using the SQL INNER JOIN statement.

What is INNER JOIN?

The INNER JOIN statement is used to combine rows from two tables when there is a matching value in both tables. Only records that satisfy the JOIN condition are included in the result.

INNER JOIN is commonly used when related information is stored in separate tables. For example, customer details can be stored in one table while their purchases are stored in another table.

Basic INNER JOIN Syntax

The ON clause specifies the columns that connect the two tables.

CREATE TABLE artists (
  artist_id INTEGER,
  artist_name TEXT
);

CREATE TABLE albums (
  album_id INTEGER,
  artist_id INTEGER,
  album_name TEXT
);

INSERT INTO artists VALUES
  (1, 'Ava'),
  (2, 'Leo'),
  (3, 'Mira');

INSERT INTO albums VALUES
  (101, 1, 'First Light'),
  (102, 2, 'Night Waves'),
  (103, 1, 'Open Roads');

SELECT
  artists.artist_name,
  albums.album_name
FROM artists
INNER JOIN albums
  ON artists.artist_id = albums.artist_id;

INNER JOIN with Table Aliases

Table aliases can make INNER JOIN queries shorter and easier to understand.

CREATE TABLE planets (
  planet_id INTEGER,
  planet_name TEXT
);

CREATE TABLE moons (
  moon_id INTEGER,
  planet_id INTEGER,
  moon_name TEXT
);

INSERT INTO planets VALUES
  (1, 'Earth'),
  (2, 'Mars'),
  (3, 'Jupiter');

INSERT INTO moons VALUES
  (101, 1, 'Moon'),
  (102, 2, 'Phobos'),
  (103, 2, 'Deimos');

SELECT
  p.planet_name,
  m.moon_name
FROM planets AS p
INNER JOIN moons AS m
  ON p.planet_id = m.planet_id;

INNER JOIN with WHERE

You can use a WHERE clause after an INNER JOIN to filter the matching records.

CREATE TABLE cafes (
  cafe_id INTEGER,
  cafe_name TEXT
);

CREATE TABLE drinks (
  drink_id INTEGER,
  cafe_id INTEGER,
  drink_name TEXT,
  price INTEGER
);

INSERT INTO cafes VALUES
  (1, 'Bean House'),
  (2, 'Morning Cup'),
  (3, 'Green Cafe');

INSERT INTO drinks VALUES
  (101, 1, 'Cold Coffee', 180),
  (102, 2, 'Green Tea', 120),
  (103, 1, 'Mocha', 220),
  (104, 3, 'Lemon Tea', 100);

SELECT
  c.cafe_name,
  d.drink_name,
  d.price
FROM cafes AS c
INNER JOIN drinks AS d
  ON c.cafe_id = d.cafe_id
WHERE d.price > 150;

INNER JOIN with ORDER BY

The ORDER BY clause can be used to sort the results returned by an INNER JOIN.

CREATE TABLE museums (
  museum_id INTEGER,
  museum_name TEXT
);

CREATE TABLE exhibits (
  exhibit_id INTEGER,
  museum_id INTEGER,
  exhibit_name TEXT,
  visitors INTEGER
);

INSERT INTO museums VALUES
  (1, 'Science Hall'),
  (2, 'History Center'),
  (3, 'Art Gallery');

INSERT INTO exhibits VALUES
  (101, 1, 'Space Lab', 850),
  (102, 2, 'Ancient Coins', 620),
  (103, 1, 'Robot Zone', 1100),
  (104, 3, 'Modern Art', 740);

SELECT
  m.museum_name,
  e.exhibit_name,
  e.visitors
FROM museums AS m
INNER JOIN exhibits AS e
  ON m.museum_id = e.museum_id
ORDER BY e.visitors DESC;

INNER JOIN with Multiple Conditions

An INNER JOIN can use more than one condition in the ON clause with the AND operator.

CREATE TABLE warehouses (
  warehouse_id INTEGER,
  warehouse_name TEXT,
  city TEXT
);

CREATE TABLE shipments (
  shipment_id INTEGER,
  warehouse_id INTEGER,
  city TEXT,
  item TEXT
);

INSERT INTO warehouses VALUES
  (1, 'North Depot', 'Delhi'),
  (2, 'West Depot', 'Pune'),
  (3, 'East Depot', 'Kolkata');

INSERT INTO shipments VALUES
  (101, 1, 'Delhi', 'Monitors'),
  (102, 2, 'Pune', 'Keyboards'),
  (103, 1, 'Mumbai', 'Printers'),
  (104, 3, 'Kolkata', 'Cables');

SELECT
  w.warehouse_name,
  s.item
FROM warehouses AS w
INNER JOIN shipments AS s
  ON w.warehouse_id = s.warehouse_id
 AND w.city = s.city;

INNER JOIN with DISTINCT

The DISTINCT keyword can be used with INNER JOIN when you want to remove duplicate values from the result.

CREATE TABLE teachers (
  teacher_id INTEGER,
  teacher_name TEXT
);

CREATE TABLE classes (
  class_id INTEGER,
  teacher_id INTEGER,
  subject TEXT
);

INSERT INTO teachers VALUES
  (1, 'Nina'),
  (2, 'Omar'),
  (3, 'Sara');

INSERT INTO classes VALUES
  (101, 1, 'Physics'),
  (102, 1, 'Physics'),
  (103, 2, 'Mathematics'),
  (104, 3, 'Physics');

SELECT DISTINCT
  t.teacher_name,
  c.subject
FROM teachers AS t
INNER JOIN classes AS c
  ON t.teacher_id = c.teacher_id;

INNER JOIN with Aggregate Functions

INNER JOIN can be combined with functions such as COUNT() and SUM() to summarize related data.

CREATE TABLE departments (
  department_id INTEGER,
  department_name TEXT
);

CREATE TABLE workers (
  worker_id INTEGER,
  department_id INTEGER,
  salary INTEGER
);

INSERT INTO departments VALUES
  (1, 'Design'),
  (2, 'Engineering'),
  (3, 'Support');

INSERT INTO workers VALUES
  (101, 1, 42000),
  (102, 1, 48000),
  (103, 2, 65000),
  (104, 2, 72000),
  (105, 3, 39000);

SELECT
  d.department_name,
  COUNT(w.worker_id) AS employee_count,
  SUM(w.salary) AS total_salary
FROM departments AS d
INNER JOIN workers AS w
  ON d.department_id = w.department_id
GROUP BY d.department_id, d.department_name
ORDER BY total_salary DESC;

INNER JOIN with Three Tables

You can use multiple INNER JOIN statements to combine three or more related tables.

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER,
  product_id INTEGER
);

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT
);

INSERT INTO customers VALUES
  (1, 'Riya'),
  (2, 'Arjun'),
  (3, 'Tara');

INSERT INTO orders VALUES
  (501, 1, 10),
  (502, 2, 20),
  (503, 1, 30);

INSERT INTO products VALUES
  (10, 'Smart Watch'),
  (20, 'Travel Bag'),
  (30, 'Desk Organizer');

SELECT
  c.customer_name,
  p.product_name
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
INNER JOIN products AS p
  ON o.product_id = p.product_id
ORDER BY c.customer_name;

INNER JOIN with Calculations

You can perform calculations on columns from joined tables.

CREATE TABLE items (
  item_id INTEGER,
  item_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  item_id INTEGER,
  quantity INTEGER,
  price INTEGER
);

INSERT INTO items VALUES
  (1, 'Notebook'),
  (2, 'Marker'),
  (3, 'Folder');

INSERT INTO sales VALUES
  (101, 1, 4, 80),
  (102, 2, 6, 40),
  (103, 3, 3, 120);

SELECT
  i.item_name,
  s.quantity,
  s.price,
  s.quantity * s.price AS total
FROM items AS i
INNER JOIN sales AS s
  ON i.item_id = s.item_id
ORDER BY total DESC;

INNER JOIN with BETWEEN

JOIN results can also be filtered using operators such as BETWEEN.

CREATE TABLE athletes (
  athlete_id INTEGER,
  athlete_name TEXT
);

CREATE TABLE performances (
  performance_id INTEGER,
  athlete_id INTEGER,
  score INTEGER
);

INSERT INTO athletes VALUES
  (1, 'Kian'),
  (2, 'Asha'),
  (3, 'Dev');

INSERT INTO performances VALUES
  (101, 1, 85),
  (102, 2, 92),
  (103, 3, 74);

SELECT
  a.athlete_name,
  p.score
FROM athletes AS a
INNER JOIN performances AS p
  ON a.athlete_id = p.athlete_id
WHERE p.score BETWEEN 80 AND 100;

INNER JOIN with IN

The IN operator can be used to filter the results of an INNER JOIN against several possible values.

CREATE TABLE vehicles (
  vehicle_id INTEGER,
  model TEXT
);

CREATE TABLE trips (
  trip_id INTEGER,
  vehicle_id INTEGER,
  destination TEXT
);

INSERT INTO vehicles VALUES
  (1, 'Comet'),
  (2, 'Falcon'),
  (3, 'Voyager');

INSERT INTO trips VALUES
  (101, 1, 'Delhi'),
  (102, 2, 'Mumbai'),
  (103, 3, 'Pune'),
  (104, 1, 'Jaipur');

SELECT
  v.model,
  t.destination
FROM vehicles AS v
INNER JOIN trips AS t
  ON v.vehicle_id = t.vehicle_id
WHERE t.destination IN ('Delhi', 'Pune');

INNER JOIN vs LEFT JOIN

The main difference is how unmatched rows are handled. INNER JOIN removes unmatched rows, while LEFT JOIN keeps all rows from the left table.

Feature	INNER JOIN	LEFT JOIN
Matching rows	Included	Included
Unmatched left rows	Excluded	Included
Unmatched right rows	Excluded	Excluded
Best use	Only related records are needed	All left-table records are needed

Advantages of INNER JOIN

Returns only related records from the joined tables.
Useful for querying relationships between tables.
Works with WHERE, GROUP BY, ORDER BY, and aggregate functions.
Can combine multiple tables in a single query.
Helps retrieve precise and relevant database information.

Best Practices

Always specify the relationship between tables using the ON clause.
Use table aliases when queries contain several tables.
Select only the columns that are required.
Use meaningful column names or aliases in the result.
Make sure JOIN columns contain compatible values.
Use indexes on frequently joined columns for better performance on larger databases.

The INNER JOIN statement is one of the most commonly used SQL JOIN operations. It allows you to combine related records from multiple tables while returning only rows that satisfy the specified matching condition.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.