SQL Joins | Academic Block

SQL JOINS

Learn how to combine related data from multiple database tables using SQL JOINs.

What is a SQL JOIN?

A JOIN is used to combine rows from two or more tables based on a related column. JOINs are one of the most important features of relational databases because related information is often stored in separate tables.

For example, a school database may store student information in one table and examination results in another. A JOIN can combine these tables using a common column such as student_id.

Basic JOIN Syntax

The basic JOIN syntax specifies the tables to combine and the columns that connect them.

CREATE TABLE players (
  player_id INTEGER,
  player_name TEXT,
  team_id INTEGER
);

CREATE TABLE teams (
  team_id INTEGER,
  team_name TEXT
);

INSERT INTO players VALUES
  (1, 'Leo', 10),
  (2, 'Maya', 20),
  (3, 'Noah', 10);

INSERT INTO teams VALUES
  (10, 'Falcons'),
  (20, 'Rockets');

SELECT
  players.player_name,
  teams.team_name
FROM players
JOIN teams
  ON players.team_id = teams.team_id;

INNER JOIN

An INNER JOIN returns only rows where a matching value exists in both tables.

CREATE TABLE airports (
  airport_id INTEGER,
  airport_name TEXT,
  city TEXT
);

CREATE TABLE flights (
  flight_id INTEGER,
  airport_id INTEGER,
  flight_code TEXT
);

INSERT INTO airports VALUES
  (1, 'Skyport Airport', 'Delhi'),
  (2, 'Harbor Airport', 'Mumbai'),
  (3, 'Mountain Airport', 'Leh');

INSERT INTO flights VALUES
  (101, 1, 'SK101'),
  (102, 2, 'HB202'),
  (103, 1, 'SK303');

SELECT
  a.airport_name,
  a.city,
  f.flight_code
FROM airports AS a
INNER JOIN flights AS f
  ON a.airport_id = f.airport_id;

INNER JOIN with WHERE

You can combine an INNER JOIN with a WHERE clause to filter the joined results.

CREATE TABLE chefs (
  chef_id INTEGER,
  chef_name TEXT
);

CREATE TABLE dishes (
  dish_id INTEGER,
  chef_id INTEGER,
  dish_name TEXT,
  price INTEGER
);

INSERT INTO chefs VALUES
  (1, 'Anika'),
  (2, 'Kabir'),
  (3, 'Rhea');

INSERT INTO dishes VALUES
  (101, 1, 'Pasta', 450),
  (102, 2, 'Curry', 380),
  (103, 1, 'Pizza', 550),
  (104, 3, 'Soup', 250);

SELECT
  c.chef_name,
  d.dish_name,
  d.price
FROM chefs AS c
INNER JOIN dishes AS d
  ON c.chef_id = d.chef_id
WHERE d.price > 400;

LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no match exists, the right-side columns contain NULL.

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

CREATE TABLE articles (
  article_id INTEGER,
  author_id INTEGER,
  title TEXT
);

INSERT INTO authors VALUES
  (1, 'Nora'),
  (2, 'Arman'),
  (3, 'Ivy'),
  (4, 'Sam');

INSERT INTO articles VALUES
  (201, 1, 'The Solar System'),
  (202, 3, 'Ocean Life'),
  (203, 1, 'Future Cities');

SELECT
  a.author_name,
  ar.title
FROM authors AS a
LEFT JOIN articles AS ar
  ON a.author_id = ar.author_id
ORDER BY a.author_id;

LEFT JOIN with NULL

A LEFT JOIN can be used with IS NULL to find records that do not have a matching record in another table.

CREATE TABLE workshops (
  workshop_id INTEGER,
  workshop_name TEXT
);

CREATE TABLE registrations (
  registration_id INTEGER,
  workshop_id INTEGER,
  participant TEXT
);

INSERT INTO workshops VALUES
  (1, 'Photography'),
  (2, 'Robotics'),
  (3, 'Astronomy'),
  (4, 'Drawing');

INSERT INTO registrations VALUES
  (101, 1, 'Ravi'),
  (102, 3, 'Meera'),
  (103, 1, 'Zoya');

SELECT
  w.workshop_name
FROM workshops AS w
LEFT JOIN registrations AS r
  ON w.workshop_id = r.workshop_id
WHERE r.registration_id IS NULL;

JOIN Using Table Aliases

Table aliases provide short names for tables and make JOIN queries easier to read.

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;

JOIN with ORDER BY

After joining tables, ORDER BY can be used to arrange the resulting rows.

CREATE TABLE games (
  game_id INTEGER,
  game_name TEXT
);

CREATE TABLE scores (
  score_id INTEGER,
  game_id INTEGER,
  points INTEGER
);

INSERT INTO games VALUES
  (1, 'Space Run'),
  (2, 'Castle Quest'),
  (3, 'Ocean Race');

INSERT INTO scores VALUES
  (101, 1, 850),
  (102, 2, 1200),
  (103, 3, 950),
  (104, 1, 1400);

SELECT
  g.game_name,
  s.points
FROM games AS g
INNER JOIN scores AS s
  ON g.game_id = s.game_id
ORDER BY s.points DESC;

JOIN with GROUP BY

JOINs can be combined with GROUP BY and aggregate functions to summarize related records.

CREATE TABLE stores (
  store_id INTEGER,
  store_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  store_id INTEGER,
  amount INTEGER
);

INSERT INTO stores VALUES
  (1, 'Central Store'),
  (2, 'North Store'),
  (3, 'West Store');

INSERT INTO sales VALUES
  (101, 1, 500),
  (102, 1, 750),
  (103, 2, 900),
  (104, 2, 400),
  (105, 3, 650);

SELECT
  st.store_name,
  SUM(s.amount) AS total_sales
FROM stores AS st
INNER JOIN sales AS s
  ON st.store_id = s.store_id
GROUP BY st.store_id, st.store_name
ORDER BY total_sales DESC;

JOIN Three Tables

You can join three or more tables when the tables are connected through related columns.

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, 'Aarav'),
  (2, 'Meera'),
  (3, 'Tara');

INSERT INTO orders VALUES
  (101, 1, 10),
  (102, 2, 20),
  (103, 1, 30);

INSERT INTO products VALUES
  (10, 'Tablet Stand'),
  (20, 'USB Hub'),
  (30, 'Desk Light');

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;

SELF JOIN

A SELF JOIN joins a table with itself. It is useful when rows within the same table have a relationship with each other.

CREATE TABLE staff (
  staff_id INTEGER,
  staff_name TEXT,
  supervisor_id INTEGER
);

INSERT INTO staff VALUES
  (1, 'Elena', NULL),
  (2, 'Jon', 1),
  (3, 'Priya', 1),
  (4, 'Samir', 2);

SELECT
  e.staff_name AS employee,
  s.staff_name AS supervisor
FROM staff AS e
LEFT JOIN staff AS s
  ON e.supervisor_id = s.staff_id
ORDER BY e.staff_id;

CROSS JOIN

A CROSS JOIN returns every possible combination of rows from the two tables.

CREATE TABLE materials (
  material TEXT
);

CREATE TABLE finishes (
  finish TEXT
);

INSERT INTO materials VALUES
  ('Wood'),
  ('Metal'),
  ('Glass');

INSERT INTO finishes VALUES
  ('Matte'),
  ('Gloss');

SELECT
  m.material,
  f.finish
FROM materials AS m
CROSS JOIN finishes AS f
ORDER BY m.material, f.finish;

FULL OUTER JOIN Alternative in SQLite

For maximum compatibility with SQLite environments, a full outer join can be simulated using two LEFT JOIN queries combined with UNION.

CREATE TABLE left_items (
  item_id INTEGER,
  item_name TEXT
);

CREATE TABLE right_items (
  item_id INTEGER,
  item_name TEXT
);

INSERT INTO left_items VALUES
  (1, 'Camera'),
  (2, 'Tripod'),
  (3, 'Microphone');

INSERT INTO right_items VALUES
  (2, 'Tripod'),
  (3, 'Microphone'),
  (4, 'Headphones');

SELECT
  l.item_id,
  l.item_name AS left_item,
  r.item_name AS right_item
FROM left_items AS l
LEFT JOIN right_items AS r
  ON l.item_id = r.item_id

UNION

SELECT
  r.item_id,
  l.item_name AS left_item,
  r.item_name AS right_item
FROM right_items AS r
LEFT JOIN left_items AS l
  ON r.item_id = l.item_id
WHERE l.item_id IS NULL
ORDER BY item_id;

JOIN with DISTINCT

The DISTINCT keyword can remove duplicate values from JOIN results.

CREATE TABLE musicians (
  musician_id INTEGER,
  musician_name TEXT
);

CREATE TABLE concerts (
  concert_id INTEGER,
  musician_id INTEGER,
  venue TEXT
);

INSERT INTO musicians VALUES
  (1, 'Lina'),
  (2, 'Marco'),
  (3, 'Sia');

INSERT INTO concerts VALUES
  (101, 1, 'City Hall'),
  (102, 1, 'City Hall'),
  (103, 2, 'River Arena'),
  (104, 3, 'City Hall');

SELECT DISTINCT
  m.musician_name,
  c.venue
FROM musicians AS m
INNER JOIN concerts AS c
  ON m.musician_id = c.musician_id
ORDER BY m.musician_name;

Common SQL JOIN Types

JOIN Type	Description	Result
INNER JOIN	Combines matching rows	Only matching records
LEFT JOIN	Keeps all rows from the left table	All left rows + matches
RIGHT JOIN	Keeps all rows from the right table	All right rows + matches
FULL OUTER JOIN	Keeps rows from both tables	Matches + unmatched rows
CROSS JOIN	Creates every combination	Cartesian product
SELF JOIN	Joins a table to itself	Related rows in one table

Advantages of SQL JOINs

Combines related information from different tables.
Reduces the need to duplicate data across tables.
Makes relational database queries more powerful.
Works with filtering, sorting, grouping, and aggregation.
Allows complex relationships between database tables to be queried.

Best Practices

Always specify the relationship between tables using ON.
Use table aliases to make long JOIN queries easier to read.
Use INNER JOIN when only matching records are required.
Use LEFT JOIN when all records from the primary table are needed.
Select only the columns required for the result.
Be careful with CROSS JOIN because it can produce many rows.

SQL JOINs allow you to connect related information stored in different tables. Understanding INNER JOIN, LEFT JOIN, CROSS JOIN, SELF JOIN, and other JOIN techniques is essential for working with relational databases.

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.