MariaDB Tutorial / Read Related Rows with INNER JOIN

INNER JOIN returns combinations of rows whose join condition is true. State the relationship keys explicitly in ON, and define whether each result row represents a customer, order or item before interpreting repeated values in a one-to-many relationship.

Define the join condition and output grain

To display a customer name next to an order, match the order’s customer_id to the customer primary key. Identical column names do not automatically create the correct relationship. Short aliases make ownership explicit and avoid ambiguous column references. The relationship design in Design Table Relationships with Keys and Foreign Keys justifies the join keys; choose Keep Unmatched Rows with LEFT JOIN when unmatched rows from the preserved side must remain.

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

Prepare the complete related data

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 c26_customers, c26_products, c26_orders, c26_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 c26_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 c26_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 c26_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_c26_order_customer FOREIGN KEY (customer_id)
  REFERENCES c26_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c26_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_c26_item_order FOREIGN KEY (order_id)
  REFERENCES c26_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c26_item_product FOREIGN KEY (product_id)
  REFERENCES c26_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c26_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c26_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c26_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 c26_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.

All four tables are supplied so this article runs independently. The queries mainly use customers and orders; the other tables preserve the shared order fixture. Cleanup must target only this article’s c26_ objects.

Join orders to their customers

SELECT o.order_id,c.customer_id,c.customer_name,o.status
FROM c26_orders AS o
INNER JOIN c26_customers AS c ON c.customer_id=o.customer_id
ORDER BY o.order_id;

Customer 1’s name appears twice because it belongs to two separate orders. This is one result row per order, not one per customer. For a unique customer population, consider an existence test or a deliberately grouped result rather than hide meaningful order rows with DISTINCT.

Separate relationship matching from business filtering

SELECT o.order_id,c.customer_name,o.ordered_at
FROM c26_orders AS o
INNER JOIN c26_customers AS c ON c.customer_id=o.customer_id
WHERE o.status='paid' ORDER BY o.order_id;

The paid order IDs are 101, 102 and 103; canceled order 104 is excluded. ON shows which customer belongs to an order, while WHERE shows which orders to display. Moving some conditions can preserve an INNER JOIN result, but an OUTER JOIN may behave differently, so keep those responsibilities clear.

Display both join keys when checking a query. Do not join on a mutable name, or on two unrelated identifiers merely because they share a column name.

Compare the real join with all possible pairs

SELECT COUNT(*) AS unconstrained_pairs
FROM c26_customers AS c CROSS JOIN c26_orders AS o;

Four customers crossed with four orders produce sixteen combinations, compared with four rows from the relationship join. CROSS JOIN is useful when every combination is intended; an accidental unrestricted combination assigns unrelated customers to orders.

The foreign keys ensure each order in this fixture has its parent, so all four orders survive the order-based INNER JOIN. Where unmatched rows are possible, INNER JOIN excludes them. The LEFT JOIN guide explains how to retain a chosen starting population and inspect absent matches.

Diagnose ambiguous columns and unexpected row counts

For Column is ambiguous, qualify shared names with o. or c. If there are too many rows, inspect key uniqueness and expected relationship multiplicity; if too few, inspect filters and missing matches. Use ORDER BY for a required result order rather than depend on physical storage.

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.c26_items;
DROP TABLE shopcourse.c26_orders;
DROP TABLE shopcourse.c26_products;
DROP TABLE shopcourse.c26_customers;