A join is a SQL operation that combines rows from two or more tables into a single result set based on a related column between them. It is the mechanism that lets you reassemble normalized data that has been split across multiple tables.
Key Points: • Joins are typically based on a foreign key relationship, matching a column in one table to a related column in another. • Common join types include INNER JOIN (only matches), LEFT/RIGHT JOIN (matches plus unmatched rows from one side), and FULL OUTER JOIN (matches plus unmatched from both sides). • Any non-trivial query pulling related data usually needs at least one join, since normalized schemas spread information across multiple tables. • The join condition is specified with an ON clause, though implicit comma-joins with a WHERE condition also work but are considered outdated style.
Example: To display each order along with the customer's name, you join the orders table to the customers table on customer_id, since the customer's name isn't stored directly in the orders table.
Code Example:
SELECT o.order_id, c.customer_name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;Interview Tip: A concise interview answer is:
"A join combines rows from two or more tables into one result set based on a shared column, usually a foreign key relationship. Since normalized databases split related data across multiple tables, joins are how you reassemble it -- for example, joining orders to customers to show the customer's name alongside each order."