SELECT Statements and Query Basics
JOIN reconstructs data from multiple normalized tables by matching rows based on key values specified in the ON condition.
From Separate Tables to One Result
A normalized database may store related information in separate tables. A JOIN reconstructs related information by matching rows from those tables. The SELECT part determines which columns appear in the result, while the ON condition defines which rows belong together.
A JOIN does not mean that every row from one table is paired with every row from the other. The ON condition provides the key-based relationship that determines the matching rows.
A Joined Result Before and After
What do you think happens?
Suppose an orders table contains order_id and customer_id, and a customers table contains customer_id and customer_name. If an order has customer_id 2 and the customer with customer_id 2 is Mira, what customer name should appear beside that order after the tables are joined?
Reveal answer
Answer: Mira
The ON condition matches the order's customer_id with the customers table's customer_id. The matching customer row supplies the related customer name.
Before the JOIN, the order information and customer information are stored in separate tables. After the JOIN, the result can contain columns selected from both tables. The result is not necessarily a new permanent table; it is the data produced by the query.
Reading the ON Condition
SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
The ON condition is the logical relationship between the tables. It typically matches a foreign key in one table to a primary key in another table.
In the example query, orders.customer_id is compared with customers.customer_id. When the values are equal, the rows are treated as related for the joined result. The key values are used for matching; the SELECT list controls which fields from those matching rows are displayed.
Reconstructing Normalized Data
Predicting the Joined Rows
Use the following two source tables and the query to predict the result. The orders table has rows (501, 2) and (502, 1), where each pair is (order_id, customer_id). The customers table has rows (1, Ava) and (2, Mira), where each pair is (customer_id, customer_name). Query: SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
Select the requested columns: The result asks for order_id from orders and customer_name from customers.
Match order 501: Order 501 has customer_id 2. The customers row with customer_id 2 has the name Mira.
Match order 502: Order 502 has customer_id 1. The customers row with customer_id 1 has the name Ava.
Assemble the result: Each matching pair contributes one result row containing the selected order identifier and customer name.
The result has the columns order_id and customer_name, with rows (501, Mira) and (502, Ava).
Normalization keeps related information in separate tables, and the JOIN supplies a way to reconstruct the relationship when it is needed. The ON condition connects the tables through their key values, while SELECT determines the shape of the returned data.
Why Integer Keys Matter
Integer keys are more efficient for joins than string-based connections. Integer keys reduce the amount of data involved and reduce comparison time. This matters because joins depend on comparing key values between tables.
Mistakes in Joined Queries
Choosing columns without understanding which table supplies them
A learner may know the desired names but overlook that order_id belongs to orders and customer_name belongs to customers.
Fix:
Track each selected column back to its source table. Writing the table name before the column, as in orders.order_id, makes the source explicit.Treating JOIN as the relationship itself
The tables have been named, but the key-based relationship has not been specified.
Fix:
Use an ON condition that identifies the related key values, such as orders.customer_id = customers.customer_id.Matching descriptive strings when integer keys are available
String-based connections involve more data and more comparison time than integer keys.
Fix:
Use the key values that define the table relationship, with integer keys being more efficient for joins than strings.Expecting every source column to appear automatically
The SELECT list determines which columns appear in the query result.
Fix:
Predict the result columns by reading the SELECT list, then predict the rows by applying the ON condition.
Practice the Matching Process
Consider these tables. orders contains (700, 3) and (701, 1), where each pair is (order_id, customer_id). customers contains (1, Noor), (2, Eli), and (3, Sam), where each pair is (customer_id, customer_name). For the query SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id, list the result columns and result rows.
Hints
- Read the SELECT list first to identify the result columns.
- For each orders row, copy its customer_id to the customers table.
- Keep only the customer name from the matching customers row.
Practice Check
Using the practice tables, determine the rows produced by the ON condition.
Match order 700: Its customer_id is 3, so it matches the customers row whose customer_id is 3. The customer name is Sam.
Match order 701: Its customer_id is 1, so it matches the customers row whose customer_id is 1. The customer name is Noor.
Read the selected fields: The query selects order_id and customer_name, so those are the result columns.
The result columns are order_id and customer_name. The result rows are (700, Sam) and (701, Noor).
Key Takeaways
- JOIN combines related rows from separate normalized tables.
- ON defines the logical relationship, typically by matching a foreign key with a primary key.
- SELECT determines which columns appear in the joined result.
- To predict result rows, match equal key values and then combine the selected fields from those rows.
- Integer keys are more efficient than string-based connections because they reduce data volume and comparison time.
Key Takeaways
- A JOIN reconstructs related information stored in multiple normalized tables.
- The ON condition matches rows by comparing related key values.
- The SELECT list determines the columns in the result.
- Integer keys improve join efficiency compared with string-based connections.
- You can predict a joined result by matching keys first and then reading the requested columns.