Tell lrnit what you want to master.Tell lrnit what you want to master.

An INNER JOIN pairs each order with the customer whose primary key equals its foreign key, and silently discards every row that finds no partner

A monthly report that joins orders to customers and comes out short has usually lost rows at the join, not in the data. An INNER JOIN keeps only rows that match on both sides, so any row without a partner vanishes with no error and no warning. Holding your own here takes three abilities, in this order: writing the join on the right key pair, predicting by hand which rows will vanish, and checking row counts against the source tables to prove nothing disappeared unexpectedly. The key pair comes first because every later check depends on it.

The key pair is a primary key (the column that uniquely identifies each row, here customers.customer_id) and a foreign key (a column in another table that stores those identifiers, here orders.customer_id). The ON clause states the matching rule. For every possible pairing of one customer row and one order row, the database keeps the pair only when the ON condition is true.

The bookstore has these rows:

INSERT INTO customers VALUES (1,'Amara Okafor','Lagos'),(2,'Ben Carter','Leeds'),(3,'Chloe Martin','Lyon'),(4,'Dev Patel','Pune');
INSERT INTO orders VALUES (101,1,'Dune',12.50),(102,1,'Emma',8.00),(103,2,'Ulysses',15.75),(104,3,'Beloved',11.20),(105,NULL,'Walden',9.40);

Traced by hand: orders 101 and 102 carry customer_id 1, so both pair with Amara. Order 103 pairs with Ben, 104 with Chloe. Order 105 carries NULL, which means "no value", and NULL equals nothing, not even another NULL, so it pairs with no one.

SELECT c.name, o.order_id, o.book_title, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_id;

The letters c and o are aliases, short names for the tables so that c.customer_id and o.customer_id can be told apart.

name | order_id | book_title | total
Amara Okafor | 101 | Dune | 12.5
Amara Okafor | 102 | Emma | 8.0
Ben Carter | 103 | Ulysses | 15.75
Chloe Martin | 104 | Beloved | 11.2

Counting rows on each side proves what was lost:

SELECT
  (SELECT COUNT(*) FROM customers) AS customers_rows,
  (SELECT COUNT(*) FROM orders) AS orders_rows,
  (SELECT COUNT(*) FROM orders o INNER JOIN customers c ON c.customer_id = o.customer_id) AS joined_rows;

customers_rows | orders_rows | joined_rows
4 | 5 | 4

Five orders went in and four came out. The missing one is Walden, 9.40, so a sales total taken from this join is short by exactly that amount. Rows drop from the customers side too, whenever a customer has no matching order.

Look at the four result rows and the four customers inserted. Which customer's name appears in customers but in no row of the join result? Type the name.

The database does not apply the declared foreign key to the join. It evaluates whatever ON says, and nothing else. Rewrite the condition as ON c.customer_id = o.order_id - 100 and the query runs without complaint, returning Dev Patel paired with order 104, a Beloved purchase that Chloe made. The foreign key constraint guards inserts, so bad orders cannot be stored. It does not rescue a join whose ON compares the wrong columns, and the wrong result looks just as tidy as the right one. Match primary key to foreign key every time, then check the counts.