Selecting Columns from Joined Tables
You'll learn how to clearly specify which table each column comes from when working with joined data.
When you join two or more tables together, both tables might have columns with the same name (like id, name, or date). Without clarification, SQL won't know which table's column you want—or worse, it might return ambiguous results.
The Core Problem
Imagine two tables: customers and orders. Both have an id column. When you join them, writing SELECT id creates confusion: which id do you mean?
The Solution: Qualify Your Columns
Table name qualification means prefixing column names with their table name using dot notation:
SELECT customers.id, customers.name, orders.id, orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;
Here, customers.id and orders.id are clearly distinguished.
Using Table Aliases for Brevity
Since typing full table names repeatedly is tedious, use aliases (short nicknames):
SELECT c.id, c.name, o.id, o.order_date
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;
Now c represents customers and o represents orders. Much cleaner!
Best Practices
- Always qualify columns in multi-table queries, even when names don't conflict—it makes your intent crystal clear
- Use consistent, meaningful aliases (e.g.,
cfor customers,ofor orders) - Qualify columns in both
SELECTandONclauses for readability
Think of it like addressing mail: "Smith" could be on any street, but "Smith on Oak Street" is unambiguous.
Key Takeaway: Qualify column names with table names or aliases (e.g., table.column or alias.column) to avoid ambiguity and improve code clarity when selecting from joined tables.