Joining Tables

Connecting the Dots

In a relational database, data isn't crammed into one giant spreadsheet. Instead, it's organized into separate, logical tables to prevent repetition and keep things tidy. This design principle is called normalization. For example, you'd have one table for Customers and another for Orders. But how do you connect a customer to their specific order? You link them using keys.

A CustomerID column exists in both tables. In the Customers table, it's the PRIMARY KEY, meaning each value is unique and identifies a specific customer. In the Orders table, it's a FOREIGN KEY, a field that points back to the primary key in another table. This link is what lets us combine, or JOIN, the tables to get a complete view.

The Inner Join

The INNER JOIN is the most common type of join. It acts like a filter, returning only the rows where the key exists in both tables. Think of it as the intersection of two sets. If a customer exists in the Customers table but has never placed an order, they won't appear in the results of an inner join with the Orders table.

An INNER JOIN finds matching records. If there's no match, the record is excluded.

The syntax is straightforward. You specify both tables and then use the ON keyword to define the connection point. For example, ON Customers.CustomerID = Orders.CustomerID tells the database to match rows where the CustomerID is the same in both tables.

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

Typing out the full table name for every column can be tedious. A common best practice is to use table aliases, which are short nicknames for your tables. You declare them right after the table name in the FROM clause. This makes your query much cleaner, especially when joining several tables.

-- Using 'c' as an alias for Customers and 'o' for Orders
SELECT
  c.CustomerName,
  o.OrderID,
  o.OrderDate
FROM Customers AS c
INNER JOIN Orders AS o
  ON c.CustomerID = o.CustomerID;

Outer Joins

What if you need to see all customers, including those who haven't placed an order? An INNER JOIN won't work because it only finds matches. This is where outer joins come in. They allow you to include rows from one table even if there are no matching rows in the other table.

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table (the one listed first) and the matched rows from the right table. If there's no match for a row from the left table, the columns from the right table will contain NULL values. This is perfect for finding things that don't have a match, like customers without orders.

-- This will list ALL customers.
-- If a customer has no orders, OrderID will be NULL.
SELECT
  c.CustomerName,
  o.OrderID
FROM Customers AS c
LEFT JOIN Orders AS o
  ON c.CustomerID = o.CustomerID;

A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image. It returns all rows from the right table and any matched rows from the left table. If a row from the right table has no match on the left, the left-side columns will be NULL. This is less common than LEFT JOIN, as you can usually achieve the same result by swapping the table order and using a LEFT JOIN.

-- This will list ALL orders.
-- If an order has an invalid CustomerID, CustomerName will be NULL.
SELECT
  c.CustomerName,
  o.OrderID
FROM Customers AS c
RIGHT JOIN Orders AS o
  ON c.CustomerID = o.CustomerID;

Finally, the FULL OUTER JOIN combines the logic of both left and right joins. It returns all rows when there is a match in either the left or the right table. It's essentially saying, "Give me everything from both tables; match them up where you can, and fill in with NULL where you can't."

This join is useful for seeing all data from two tables at once, regardless of whether a link exists.

-- Lists all customers and all orders.
-- NULLs will appear on the right for unmatched customers.
-- NULLs will appear on the left for unmatched orders.
SELECT
  c.CustomerName,
  o.OrderID
FROM Customers AS c
FULL OUTER JOIN Orders AS o
  ON c.CustomerID = o.CustomerID;

Choosing Your Join Condition

So far, we've used the ON clause to specify how tables should be linked. This is the most common and flexible method, as it allows for complex conditions.

However, there's another option: the USING clause. You can use USING as a shorthand when the columns you're joining on have the exact same name in both tables. Instead of writing ON c.CustomerID = o.CustomerID, you can simply write USING (CustomerID).

-- This query is equivalent to our first INNER JOIN example.
SELECT
  CustomerName,
  OrderID
FROM Customers
INNER JOIN Orders
  USING (CustomerID);

While USING can make queries shorter, ON is generally preferred because it's more explicit and versatile. It doesn't depend on column names being identical and can handle joins on multiple columns or more complex logic.

Now you're ready to start combining data from across your database.

Time to test your knowledge.

Quiz Questions 1/6

What is the primary goal of normalization in a relational database?

Quiz Questions 2/6

You need to retrieve a list of all customers, along with any orders they may have placed. Crucially, customers who have never placed an order must be included in the result. Which join should you use?

Intermediate SQL Mastery