SovranCode
SQL: Query, Model, and Analyze Data INNER, LEFT, RIGHT, and FULL Joins
This device
Course contentsINNER, LEFT, RIGHT, and FULL Joins · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONCommon Table Expressions
NEXT LESSONSelf joins and many-to-many data
Combining related data · Lesson 13 140 min

INNER, LEFT, RIGHT, and FULL Joins

A join answers a question that needs facts from more than one table. The join type decides which unmatched rows are still meaningful enough to keep; the ON condition decides which row pairs are allowed to match.

What you will leave with

You will predict a join's retained rows, write clear join conditions with aliases, preserve zero-activity customers, audit orphaned records, and check result grain before a join inflates a total.

Choose rows before you choose columns

Think of a join as lining up two lists using a shared label. Here, the label is customer_id: a customer row can line up with each order that carries the same ID. The important question is not “which keyword do I remember?” It is “which list is my report promising to include, even when no partner row exists?” Answer that first, then choose the join.

MatchConnect rows through their related keys.
PreserveDecide which unmatched rows still belong.
AuditRead NULLs as a missing match, not a zero.
VerifyCheck the result grain before you aggregate.
LEFT INPUTcustomers5 rows

Celia and Elias have no order.

MATCHcustomer_idc.id = o.customer_id

The relationship chooses valid pairs.

RIGHT INPUTorders5 rows

Order 105 has no matching customer.

A quick way to choose a join

Use INNER JOIN when a row is useful only with a partner. Use LEFT JOIN when your question starts with the left table—for example, “show every customer and any orders they have.” Use RIGHT JOIN only when reading the query from the right is clearer. Use FULL OUTER JOIN when you are comparing two systems and need to find what exists on either side but not both.

INNER JOIN keeps only matching pairs

An INNER JOIN returns a row only when the ON condition is true for a row from each input. The seed has five customers and five orders, but this result has four rows: Celia and Elias have no order, while order 105 references a customer that is absent. Neither unmatched side belongs in an inner-join answer.

SQL BROWSER RUNNER

Read customers with matching orders

Return only customer-order pairs that share the same customer ID.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO customers (customer_id, customer_name, city) VALUES
  (1, 'Amina', 'Rabat'),
  (2, 'Bilal', 'Casablanca'),
  (3, 'Celia', 'Fes'),
  (4, 'Dina', 'Rabat'),
  (5, 'Elias', 'Tangier');

INSERT INTO orders (order_id, customer_id, status, amount_cents) VALUES
  (101, 1, 'paid', 3500),
  (102, 1, 'pending', 800),
  (103, 2, 'paid', 2200),
  (104, 4, 'paid', 6000),
  (105, 99, 'paid', 900); -- deliberately unmatched: an import problem to audit

SELECT c.customer_name, o.order_id, o.status, o.amount_cents
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the SELECT list to include c.city. Then remove the ORDER BY and notice that displayed order is no longer a contract.

Qualify shared column names

Both tables have customer_id, so write c.customer_id and o.customer_id. Aliases make the source of every key clear and prevent an ambiguous-column error.

LEFT JOIN keeps the complete left-side population

A LEFT JOIN retains every row from the table on its left. When no order matches, the order columns are NULL. A customer can still appear more than once: Amina has two orders, so she produces two joined rows. The result grain is customer-order pair, not one row per customer.

SQL BROWSER RUNNER

Keep customers without orders

Preserve every customer while adding order details where a match exists.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO customers (customer_id, customer_name, city) VALUES
  (1, 'Amina', 'Rabat'),
  (2, 'Bilal', 'Casablanca'),
  (3, 'Celia', 'Fes'),
  (4, 'Dina', 'Rabat'),
  (5, 'Elias', 'Tangier');

INSERT INTO orders (order_id, customer_id, status, amount_cents) VALUES
  (101, 1, 'paid', 3500),
  (102, 1, 'pending', 800),
  (103, 2, 'paid', 2200),
  (104, 4, 'paid', 6000),
  (105, 99, 'paid', 900); -- deliberately unmatched: an import problem to audit

SELECT c.customer_name, c.city, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Find Celia and Elias. Why do they have NULL order values instead of a made-up order ID?

A filter on the nullable side belongs in ON when unmatched rows matter

This is one of the most important outer-join decisions. Put o.status = 'paid' in ON when the question is “show every customer and their paid orders, if any.” That limits matching orders but still preserves every customer. In contrast, a WHERE o.status = 'paid' runs after the join and removes the NULL order rows—silently defeating the reason for the left join.

SQL BROWSER RUNNER

Preserve every customer with paid-order matches

Put the paid-order condition in ON so zero-paid-order customers remain visible.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO customers (customer_id, customer_name, city) VALUES
  (1, 'Amina', 'Rabat'),
  (2, 'Bilal', 'Casablanca'),
  (3, 'Celia', 'Fes'),
  (4, 'Dina', 'Rabat'),
  (5, 'Elias', 'Tangier');

INSERT INTO orders (order_id, customer_id, status, amount_cents) VALUES
  (101, 1, 'paid', 3500),
  (102, 1, 'pending', 800),
  (103, 2, 'paid', 2200),
  (104, 4, 'paid', 6000),
  (105, 99, 'paid', 900); -- deliberately unmatched: an import problem to audit

SELECT c.customer_name, o.order_id, o.amount_cents
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
ORDER BY c.customer_id, o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Amina has one paid and one pending order. Confirm that only the paid one matches while Celia and Elias remain.

SQL BROWSER RUNNER

See how WHERE removes unmatched customers

Run the superficially similar query with the same filter after the join.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO customers (customer_id, customer_name, city) VALUES
  (1, 'Amina', 'Rabat'),
  (2, 'Bilal', 'Casablanca'),
  (3, 'Celia', 'Fes'),
  (4, 'Dina', 'Rabat'),
  (5, 'Elias', 'Tangier');

INSERT INTO orders (order_id, customer_id, status, amount_cents) VALUES
  (101, 1, 'paid', 3500),
  (102, 1, 'pending', 800),
  (103, 2, 'paid', 2200),
  (104, 4, 'paid', 6000),
  (105, 99, 'paid', 900); -- deliberately unmatched: an import problem to audit

SELECT c.customer_name, o.order_id, o.amount_cents
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.customer_id, o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Compare this output with the prior runner. Which customers disappeared, and why?

RIGHT JOIN is the mirror of LEFT JOIN

A RIGHT JOIN preserves every row from the table written on the right. This example retains all orders, including order 105 whose customer is missing. Many teams standardize on left joins because they can write the preserved table first, but the meaning is the same once you reverse the inputs. Read the query from the preserved table outward rather than memorizing keywords.

SQL BROWSER RUNNER

Keep every order, including unmatched imports

Use RIGHT JOIN to retain the orders table and reveal a missing customer match.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO customers (customer_id, customer_name, city) VALUES
  (1, 'Amina', 'Rabat'),
  (2, 'Bilal', 'Casablanca'),
  (3, 'Celia', 'Fes'),
  (4, 'Dina', 'Rabat'),
  (5, 'Elias', 'Tangier');

INSERT INTO orders (order_id, customer_id, status, amount_cents) VALUES
  (101, 1, 'paid', 3500),
  (102, 1, 'pending', 800),
  (103, 2, 'paid', 2200),
  (104, 4, 'paid', 6000),
  (105, 99, 'paid', 900); -- deliberately unmatched: an import problem to audit

SELECT c.customer_name, o.order_id, o.customer_id AS order_customer_id
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Rewrite the query as a LEFT JOIN by putting orders first. The rows should represent the same answer.

FULL OUTER JOIN preserves unmatched rows from both sides

A FULL OUTER JOIN combines the two preservation rules: matched pairs appear together, customers without orders remain, and orders without customers remain. It is valuable for reconciliation and migration audits. Not every SQL engine offers this syntax, so check your target database before depending on it; the course runner supports this example. COALESCE gives the final sort a value when one side is NULL.

SQL BROWSER RUNNER

Audit both sides of a relationship

Keep matches, customer-only rows, and the orphaned order in one result.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO customers (customer_id, customer_name, city) VALUES
  (1, 'Amina', 'Rabat'),
  (2, 'Bilal', 'Casablanca'),
  (3, 'Celia', 'Fes'),
  (4, 'Dina', 'Rabat'),
  (5, 'Elias', 'Tangier');

INSERT INTO orders (order_id, customer_id, status, amount_cents) VALUES
  (101, 1, 'paid', 3500),
  (102, 1, 'pending', 800),
  (103, 2, 'paid', 2200),
  (104, 4, 'paid', 6000),
  (105, 99, 'paid', 900); -- deliberately unmatched: an import problem to audit

SELECT c.customer_id AS customer_id, c.customer_name,
       o.order_id, o.customer_id AS order_customer_id
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY COALESCE(c.customer_id, o.customer_id), o.order_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Identify the two customer-only rows and the one order-only row before you run it.

The orphan is deliberate teaching data

Order 105 exists only to make the full and right joins visible. In a production schema, a foreign key should normally prevent this kind of orphaned order from being written.

How to read the NULLs: a NULL order ID beside Celia means “Celia has no matching order in this result,” not that an order exists with an ID of zero. A NULL customer name beside order 105 means the order's customer reference could not be matched. This distinction matters: report missing matches clearly, and only replace NULL with a display value such as “No order yet” after you understand what the NULL represents.

A second one-to-many join can multiply rows

Joins return one row per matching pair. When an order has several line items, joining orders to lines repeats the order's amount for each line. That is correct at line-item grain, but summing o.amount_cents after this join would overcount order 201 and 203. Before using SUM, say what one row represents and aggregate or pre-aggregate at that grain.

SQL BROWSER RUNNER

Inspect order-line result grain

Join orders to their line items and observe which order values repeat.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount_cents INTEGER NOT NULL
);

CREATE TABLE order_lines (
  line_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount_cents) VALUES
  (201, 1, 3500),
  (202, 1, 2200),
  (203, 2, 6000);

INSERT INTO order_lines (line_id, order_id, product_name, quantity) VALUES
  (1, 201, 'Notebook', 1),
  (2, 201, 'Pen set', 2),
  (3, 202, 'Backpack', 1),
  (4, 203, 'Monitor stand', 1),
  (5, 203, 'Cable', 2);

SELECT o.order_id, o.customer_id, o.amount_cents,
       line.product_name, line.quantity
FROM orders AS o
JOIN order_lines AS line ON line.order_id = o.order_id
ORDER BY o.order_id, line.line_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Count the rows for orders 201 and 203. Explain why SUM(amount_cents) in this result would be wrong without a deliberate fix.

Common join mistakes

WHERE on nullable side

Outer rows vanish

Place a match-only filter in ON when the report must keep rows with no match.

SELECT *

Unclear output

Name columns and aliases. Joined tables often share IDs, dates, status fields, and other confusing names.

No result grain

Inflated totals

One-to-many joins repeat parent values. Inspect pairs before writing aggregates.

Missing ON condition

Cartesian product

An accidental cross join pairs every left row with every right row. Check expected row counts early.

Independent lab: customer paid-order totals

Build a report with every customer's count and total of paid orders. Amina should have one paid order worth 3,500 cents; Dina should have one worth 6,000; Celia and Elias should remain with zero. The unmatched import order must not appear because this report starts from the customer population. Use COUNT(o.order_id), not COUNT(*), and turn a missing sum into zero with COALESCE. In a real dashboard, this pattern answers “who has not purchased yet?” as reliably as it answers “who has purchased?”

SQL BROWSER RUNNER

Audit a customer paid-order report

Preserve every customer, then count and total only their paid orders.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL
);

INSERT INTO customers (customer_id, customer_name, city) VALUES
  (1, 'Amina', 'Rabat'),
  (2, 'Bilal', 'Casablanca'),
  (3, 'Celia', 'Fes'),
  (4, 'Dina', 'Rabat'),
  (5, 'Elias', 'Tangier');

INSERT INTO orders (order_id, customer_id, status, amount_cents) VALUES
  (101, 1, 'paid', 3500),
  (102, 1, 'pending', 800),
  (103, 2, 'paid', 2200),
  (104, 4, 'paid', 6000),
  (105, 99, 'paid', 900); -- deliberately unmatched: an import problem to audit

-- Keep every customer, then summarize only their paid orders.
SELECT c.customer_id, c.customer_name,
       COUNT(o.order_id) AS paid_order_count,
       COALESCE(SUM(o.amount_cents), 0) AS paid_cents
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
GROUP BY c.customer_id, c.customer_name
ORDER BY paid_cents DESC, c.customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the status in ON from paid to pending. Predict which totals change while every customer still appears.

Lab review criteria

The customer table is the preserved left input. The paid condition is in ON, so no-paid customers survive. COUNT(o.order_id) ignores the NULL placeholder and COALESCE makes a missing total explicit without confusing it with a stored zero.

Lesson review

Joins are choices about meaning, not just syntax. Start with the population the question promises to keep, write the relationship in ON, place filters at the correct query stage, then inspect the result grain before you count or sum. A NULL from an outer join is evidence of a missing match that deserves a deliberate business decision.

  • I can predict which rows an INNER and LEFT JOIN keep.
  • I can preserve unmatched rows while matching only a filtered right-side subset.
  • I can read RIGHT and FULL joins as preservation choices.
  • I can explain why one-to-many joins repeat parent values.
  • I can write a zero-activity report without erasing its zero-activity rows.
KNOWLEDGE CHECK

Check join reasoning

Answer all ten questions, then revisit any example whose retained rows or result grain surprised you.

01What does an INNER JOIN return?
02Which rows does a LEFT JOIN preserve even when no right-side row matches?
03Where should a paid-order condition go when a report must still show customers with no paid orders?
04Why can WHERE o.status = 'paid' after a LEFT JOIN accidentally behave like an INNER JOIN?
05What is true about RIGHT JOIN?
06What does FULL OUTER JOIN preserve?
07A customer has three matching orders. How many joined rows does that customer produce before grouping?
08Why use qualified names such as c.customer_id and o.customer_id?
09Which expression counts real matching orders in a LEFT JOIN report?
10What should you inspect before summing values after multiple joins?
PREVIOUS LESSONCommon Table Expressions
NEXT LESSONSelf joins and many-to-many data
ON THIS PAGEINNER, LEFT, RIGHT, and FULL JoinsLesson mapINNER JOINLEFT JOINON vs WHERERIGHT JOINFULL OUTER JOINResult grainCommon mistakesIndependent labLesson reviewKnowledge check
Course contents