MariaDB Tutorial / Keep Unmatched Rows with LEFT JOIN

LEFT JOIN preserves rows from the left table, fills matching right-side columns when available and returns NULL for the right side when no match exists. Choose the population to retain first, then place filters carefully so customers without qualifying orders are not removed accidentally.

Put the population to preserve on the left

For every registered customer and their order activity, start with customers. An unmatched right side produces NULL in the query result; it does not create a stored order or rewrite an existing order to NULL. Multiple matching orders still produce multiple result rows. Compare Read Related Rows with INNER JOIN when only matching combinations are needed; Use Subqueries, EXISTS, and NOT EXISTS can express the absence of a qualifying order with NOT EXISTS.

Check the MariaDB documentation for the syntax and conditions used here: join syntax.

Prepare customers with different order histories

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c27_customers, c27_products, c27_orders, c27_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c27_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c27_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c27_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c27_order_customer FOREIGN KEY (customer_id)
  REFERENCES c27_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c27_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c27_item_order FOREIGN KEY (order_id)
  REFERENCES c27_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c27_item_product FOREIGN KEY (product_id)
  REFERENCES c27_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c27_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c27_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c27_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c27_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Customer 4 has no orders, while customer 3 has only a canceled order. Decide what “has not purchased” means before selecting one of these populations.

Inspect preserved customer rows and right-side NULLs

SELECT c.customer_id,c.customer_name,o.order_id,o.status
FROM c27_customers AS c LEFT JOIN c27_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;

The result contains five rows: two for customer 1 and one each for customers 2, 3 and 4. Only customer 4’s order columns are NULL; the customer identifier and name remain. Expecting exactly four result rows would confuse customers with customer–order combinations.

Compare status filtering in ON with filtering in WHERE

SELECT c.customer_id,COUNT(o.order_id) AS paid_orders
FROM c27_customers AS c LEFT JOIN c27_orders AS o
 ON o.customer_id=c.customer_id AND o.status='paid'
GROUP BY c.customer_id ORDER BY c.customer_id;

Because the paid condition belongs to ON, customers without paid matches remain. Moving it to WHERE o.status='paid' removes the null-extended rows and canceled-only customers. Retain the ON filter when the report must display customers 3 and 4 as zero.

Move the same paid condition to WHERE to compare the result directly. This query retains only customers 1 and 2, losing the zero-order results for customers 3 and 4 from the preceding table.

SELECT c.customer_id,COUNT(o.order_id) AS paid_orders
FROM c27_customers c LEFT JOIN c27_orders o
 ON o.customer_id=c.customer_id
WHERE o.status='paid'
GROUP BY c.customer_id ORDER BY c.customer_id;

Change only the filter position, compare customer rows and counts, then restore the ON predicate when every customer must remain.

Count the right-side key rather than the preserved row

Even a customer without an order has one LEFT JOIN result row, so COUNT(*) can incorrectly produce one. COUNT(o.order_id) counts only matched orders because that right-side primary key cannot be NULL in an actual order. Counting the always-present customer ID does not solve this problem.

SELECT c.customer_id FROM c27_customers AS c
LEFT JOIN c27_orders AS o ON o.customer_id=c.customer_id
WHERE o.order_id IS NULL ORDER BY c.customer_id;

Without a status restriction, the missing-match query returns only customer 4. Adding a paid restriction to ON changes it to customers 3 and 4. Test absence with a nonnullable right-side key, not a nullable note that could also be missing from a genuine matching order.

Review preservation, filter position and aggregation

Before applying DISTINCT to an unexpected result, check what the LEFT JOIN should preserve, whether a WHERE condition rejects NULLs and what each row represents. Read the initial keys and statuses when counts differ from the expected fixture. The COUNT and NULL discussion explains the aggregate behavior used here.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c27_items;
DROP TABLE shopcourse.c27_orders;
DROP TABLE shopcourse.c27_products;
DROP TABLE shopcourse.c27_customers;