Joining Related Tables with SQL

The Foundation of Relationships: Primary and Foreign Keys

Before we can join tables, we need to understand how they're related. In a relational database, we use keys to establish these connections.

• A Primary Key (PK) is a column (or set of columns) that uniquely identifies each row in a table. In our users table, the id column is the primary key.
• A Foreign Key (FK) is a column in one table that refers to the primary key in another table. In our tasks table, the user_id column is a foreign key that references the id column in the users table.

This PK-FK relationship is the "glue" that allows us to link a specific task back to the user who owns it.

The Workhorse: INNER JOIN

The most common type of join is the INNER JOIN. It combines rows from two tables and returns only those rows where the join condition is met—in other words, where there is a match in both tables.

The basic syntax looks like this:

SELECT
  table1.column1,
  table2.column2
FROM
  table1
INNER JOIN table2 ON table1.foreign_key = table2.primary_key;

Let's use this to get a list of all tasks along with the email of the user who owns each task.

SELECT
  tasks.title,
  users.email
FROM
  tasks
INNER JOIN users ON tasks.user_id = users.id;

This query tells the database:

1. Look at the tasks table.
2. Join it with the users table.
3. The connection between them is where a task's user_id matches a user's id.
4. For every successful match, give me the task's title and the user's email.

Using Aliases for Cleaner Queries

As queries get more complex, typing table_name.column_name repeatedly becomes tedious. You can assign short, temporary names to your tables called aliases right in the FROM clause. This is standard practice and makes queries much more readable.

Here is the same query as before, but with table aliases (t for tasks and u for users):

SELECT
  t.title,
  u.email
FROM
  tasks AS t
INNER JOIN users AS u ON t.user_id = u.id;

Note: The AS keyword is optional for table aliases in PostgreSQL, so FROM tasks t works just as well.

SELECT
  t.title
FROM
  tasks AS t
INNER JOIN users AS u ON t.user_id = u.id
WHERE
  u.email = 'alice@example.com';

Seeing More with LEFT JOIN and RIGHT JOIN

What if you want to list all users and see what tasks they have, including users who might not have any tasks yet? An INNER JOIN won't work because it would exclude users with no matching tasks.

This is where LEFT JOIN comes in.

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table (the one in the FROM clause), and the matched rows from the right table. If there is no match, the columns from the right table will be NULL.

-- Get all users and their task titles, if any
SELECT
  u.email,
  t.title
FROM
  users AS u
LEFT JOIN tasks AS t ON u.id = t.user_id;

This will list every user. If a user has tasks, it will create a row for each task. If a user has no tasks, they will still appear once, but the title column will be NULL.

A RIGHT JOIN does the opposite: it returns all rows from the right table and any matching rows from the left. In practice, RIGHT JOIN is used less frequently because you can almost always achieve the same result with a LEFT JOIN just by swapping the order of the tables.

The "Anti-Join" pattern you just read about is extremely useful. For example, to find all users who have not created any tasks, you can use this query:

SELECT u.email
FROM users AS u
LEFT JOIN tasks AS t ON u.id = t.user_id
WHERE t.id IS NULL; -- Or t.user_id, or any other column from the tasks table

The Complete Picture: FULL OUTER JOIN

Finally, what if you want to see everything from both tables? The FULL OUTER JOIN combines the results of both LEFT and RIGHT joins. It returns all rows from both tables, placing NULLs where matches don't exist on either side.

SELECT u.email, t.title
FROM users AS u
FULL OUTER JOIN tasks AS t ON u.id = t.user_id;

This would show:

• Users and their matched tasks.
• Users who have no tasks.
• Tasks that have no associated user (which isn't possible with our current schema, but could be in other scenarios).

A Visual Summary of Joins

The different join types can be confusing at first. Venn diagrams are a great way to visualize what data each join type includes.

Joining Multiple Tables

You are not limited to joining only two tables. You can chain JOIN clauses to connect several tables in a single query. Imagine we also had a projects table, and each task belonged to a project.

The query to get the user, project, and task information together would look like this:

SELECT
  u.email,
  p.name AS project_name,
  t.title AS task_title
FROM
  users AS u
INNER JOIN tasks AS t ON u.id = t.user_id
INNER JOIN projects AS p ON t.project_id = p.id;

Notice the use of column aliases (AS project_name) here. This is useful when you have columns with the same name in different tables (like name or id) or when you want to make your result set more readable.

Conclusion

You've now unlocked the ability to query data across your entire relational schema. JOINs are the heart of relational databases, allowing you to piece together normalized data into the rich, complex information your application needs.

Key Takeaways:

• Relationships are defined by Primary Keys (PK) and Foreign Keys (FK).
• INNER JOIN is for matching rows only. It's the most common join.
• LEFT JOIN is for when you want all records from the "left" table, regardless of whether they have a match in the right table.
• The LEFT JOIN ... WHERE key IS NULL pattern is perfect for finding records that don't have a corresponding record in another table.
• FULL OUTER JOIN gives you everything from both tables.
• Use table aliases (users u) and column aliases (u.name AS user_name) to write clean, unambiguous, and readable queries.

Preview of the Next Lesson:
Now that you can write complex queries to retrieve data, the next step is to make them secure. When building a web application, user input can't be trusted. If you insert it directly into your SQL queries, you open your application to a massive vulnerability called SQL Injection. In our next lesson, you will learn how to write parameterized queries, the standard defense against this critical threat.