Joining on primary key to foreign key in SQL - Step-by-Step Execution

Concept Flow - Joining on primary key to foreign key

Start with two tables

↓

Identify primary key in Table A

↓

Identify foreign key in Table B

↓

Match rows where TableA.primary_key = TableB.foreign_key

↓

Combine matched rows into one result

↓

Output joined table with columns from both tables

We start with two tables, find the primary key in one and the matching foreign key in the other, then combine rows where these keys match to create a joined result.

Execution Sample

SQL

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

This query joins Orders and Customers tables by matching Orders.CustomerID (foreign key) with Customers.CustomerID (primary key) to list orders with customer names.

Execution Table

Step	Action	Orders Row	Customers Row	Join Condition	Result Row
1	Check first Orders row	OrderID=101, CustomerID=1	CustomerID=1, CustomerName='Alice'	1 = 1 (True)	OrderID=101, CustomerName='Alice'
2	Check second Orders row	OrderID=102, CustomerID=2	CustomerID=1, CustomerName='Alice'	2 = 1 (False)	No row
3	Check second Orders row	OrderID=102, CustomerID=2	CustomerID=2, CustomerName='Bob'	2 = 2 (True)	OrderID=102, CustomerName='Bob'
4	Check third Orders row	OrderID=103, CustomerID=3	CustomerID=1, CustomerName='Alice'	3 = 1 (False)	No row
5	Check third Orders row	OrderID=103, CustomerID=3	CustomerID=2, CustomerName='Bob'	3 = 2 (False)	No row
6	Check third Orders row	OrderID=103, CustomerID=3	CustomerID=3, CustomerName='Charlie'	3 = 3 (True)	OrderID=103, CustomerName='Charlie'
7	No more Orders rows				End of join

💡 All Orders rows checked against Customers rows; join completed.

Variable Tracker

Variable	Start	After Step 1	After Step 3	After Step 6	Final
Current Orders Row	None	OrderID=101, CustomerID=1	OrderID=102, CustomerID=2	OrderID=103, CustomerID=3	None (end)
Current Customers Row	None	CustomerID=1, CustomerName='Alice'	CustomerID=2, CustomerName='Bob'	CustomerID=3, CustomerName='Charlie'	None (end)
Result Rows	Empty	[(101, 'Alice')]	[(101, 'Alice'), (102, 'Bob')]	[(101, 'Alice'), (102, 'Bob'), (103, 'Charlie')]	Final result set

Key Moments - 3 Insights

Why do we compare Orders.CustomerID to Customers.CustomerID?

Because Orders.CustomerID is a foreign key that references Customers.CustomerID, the primary key. The join matches rows where these keys are equal, as shown in execution_table steps 1, 3, and 6.

Why do some Orders rows check multiple Customers rows?

Because the join tries to find a matching Customers row for each Orders row. If the first Customers row doesn't match (false condition), it checks the next one until it finds a match or runs out of rows, as seen in steps 2, 4, and 5.

What happens if no matching Customers row is found?

No joined row is produced for that Orders row. The execution_table shows 'No row' in the Result Row column when the join condition is false.

Visual Quiz - 3 Questions

Look at the execution table, what is the Result Row at Step 3?

A OrderID=102, CustomerName='Alice'

B OrderID=102, CustomerName='Bob'

C No row

D OrderID=101, CustomerName='Alice'

💡 Hint

Check the Result Row column in execution_table at Step 3.

At which step does the join find the match for OrderID=103?

A Step 4

B Step 5

C Step 6

D Step 7

💡 Hint

Look for OrderID=103 in Orders Row and a true join condition in execution_table.

If Orders had a row with CustomerID=4, what would happen in the join?

A It would produce no joined row because no matching Customers row exists

B It would join with Customers.CustomerID=4 row

C It would cause an error

D It would join with the first Customers row

💡 Hint

Refer to key_moments about what happens when no matching Customers row is found.

Concept Snapshot

JOINING ON PRIMARY KEY TO FOREIGN KEY

- Use JOIN with ON clause: TableA.primary_key = TableB.foreign_key
- Matches rows where keys are equal
- Combines columns from both tables in result
- Rows without matches are excluded in INNER JOIN
- Common for linking related data across tables

Full Transcript

This visual execution shows how SQL joins two tables by matching a primary key in one table with a foreign key in another. We start with Orders and Customers tables. For each Orders row, we check Customers rows to find where Orders.CustomerID equals Customers.CustomerID. When they match, we combine the rows into one result row with order ID and customer name. Steps 1, 3, and 6 show successful matches. Steps 2, 4, and 5 show checks that fail and produce no result. The process continues until all Orders rows are checked. This method links related data across tables using keys, producing a combined view of information.