0%
DQDigital Qasas Courses/SQL for Professionals
Sign inAll courses
  • What SQL is, and why professionals learn it
  • Learn faster: pair this course with a free AI assistant
  • SELECT and FROM: your first real query
  • WHERE: keep only the rows you want
  • Combine conditions: AND, OR, IN, BETWEEN, LIKE, NULL
  • ORDER BY, LIMIT, and DISTINCT
  • COUNT, SUM, AVG, MIN, MAX
  • GROUP BY: one number per group
  • HAVING: filter the groups
  • Why tables relate: keys and joins
  • INNER JOIN: matching rows from two tables
  • LEFT JOIN: keep everything, find what is missing
  • Subqueries: a query inside a query
  • CASE: if-and-then inside a query
  • Window functions: a first look
  • Putting it together, and a cheat sheet
  • Practice challenges
Combine tables with joins

INNER JOIN: matching rows from two tables

Last updated 14 June 2026

A join combines rows from two tables where a condition holds. The plain JOIN (also called INNER JOIN) keeps only rows that have a match on both sides. You name the link in the ON clause.

SELECT o.id, c.name, c.city
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id;

Two things make joins readable. Give each table a short alias (o, c) and prefix columns with it. The ON clause states which columns must match.

Every order with the customer's name and city.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT o.id, c.name, c.city, o.status
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id;

Joins are not limited to two tables. To get product names onto order lines, join order_items to products.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT oi.order_id, p.name, oi.quantity
FROM order_items AS oi
JOIN products AS p ON oi.product_id = p.id;

Your turn

Return every order's id, the customer's name, and the order status, by joining orders to customers.

Join orders to customers; show order id, customer name, status.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT o.id, c.name, o.status
FROM orders AS o
JOIN customers AS c ON ;
SELECT o.id, c.name, o.status FROM orders AS o JOIN customers AS c ON o.customer_id = c.id;
Forget the ON clause and many databases pair every left row with every right row. With 7 orders and 6 customers that is 42 rows of nonsense. Always state how the tables match.

What does an INNER JOIN do with a row that has no match on the other table?

  • Drops it from the result
  • Keeps it, filling the other columns with NULL
  • Raises an error
An inner join only keeps matched pairs. Rows with no partner are left out. A LEFT JOIN, next lesson, is what keeps them.

Joins are where most people stall. If a join is not behaving, paste the table definitions, your query, and what you expected into your AI assistant and ask: Why is this join returning the wrong rows? Then run its suggestion here and check it before you trust it.

← PreviousWhy tables relate: keys and joinsNext →LEFT JOIN: keep everything, find what is missing