A Quick Guide to SQL — Chapter 4: Querying Multiple Tables with JOIN

5 min readJun 13, 2023

This image is generated by AI for the 4th chapter of a Quick Guide to SQL.

In the previous chapter we learned about manipulating data in SQL. In this lesson, we will delve into the concept of joining multiple tables in SQL. Joining tables allows us to combine related data from different tables based on common columns. We will explore various types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. By the end of this lesson, you will be equipped to retrieve data from multiple tables using different join types. Let’s begin!

Chapters of This Series

1. Understanding Table Relationships

Before we dive into joins, it is crucial to understand the relationships between tables. In a relational database, tables are designed to store related data, and the relationships between tables are established using keys. These relationships define how the data in one table is associated with the data in another table.

There are three common types of relationships in database design:

One-to-One Relationship

In a one-to-one relationship, each record in one table is associated with exactly one record in another table, and vice versa. This type of relationship is relatively rare in database design and is typically used when the related data is distinct and separate. For example, a table of employees may have a one-to-one relationship with a table of employee contact information.

One-to-Many Relationship

In a one-to-many relationship, a record in one table can be associated with multiple records in another table, but each record in the second table is associated with only one record in the first table. This is the most common type of relationship in database design. For example, a table of customers may have a one-to-many relationship with a table of orders, where each customer can have multiple orders, but each order is associated with only one customer.

Many-to-Many Relationship

In a many-to-many relationship, multiple records in one table can be associated with multiple records in another table, and vice versa. This type of relationship requires an intermediary table, often called a junction or linking table, to store the associations between the two tables. For example, consider a database for a bookstore. A book can be written by multiple authors, and an author can write multiple books. To represent this many-to-many relationship, we need an intermediary table that connects the books and authors tables.

These relationships are defined by establishing primary and foreign keys in the tables. A primary key is a unique identifier for each record in a table, while a foreign key is a field in one table that refers to the primary key in another table. By using primary and foreign keys, tables can be related to each other, allowing for the establishment of meaningful relationships and the retrieval of data across multiple tables using SQL joins.

Understanding these table relationships is crucial when working with joins, as it helps determine which type of join to use based on the relationship between the tables. It allows you to effectively query and retrieve data from related tables, ensuring data integrity and consistency within the database.

2. INNER JOIN — Retrieving Data from Multiple Tables

The INNER JOIN allows us to retrieve data that exists in both tables based on a common column. Let’s say we have two tables: “orders” and “customers” with a common column “customer_id.” To retrieve customer names along with their corresponding orders, use the following syntax:

SELECT customers.name, orders.order_number
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

This statement joins the “customers” and “orders” tables based on the “customer_id” column, and retrieves the customer names and their respective order numbers.

The INNER JOIN is the default type of join, so if you omit the keyword “INNER,” the query will still perform an inner join between the “customers” and “orders” tables. Both versions of the code will produce the same result.

SELECT customers.name, orders.order_number
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

3. LEFT JOIN — Including All Rows from the Left Table

The LEFT JOIN retrieves all rows from the left table (the one specified before JOIN) and matching rows from the right table. If no match is found, NULL values are returned for the right table columns. For example, to retrieve all customers along with their orders (if any), use the following syntax:

SELECT customers.name, orders.order_number
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

This statement returns all customers, including those without any orders.

4. RIGHT JOIN — Including All Rows from the Right Table

The RIGHT JOIN is similar to the LEFT JOIN but includes all rows from the right table and matching rows from the left table. If no match is found, NULL values are returned for the left table columns. Let’s modify the previous example to use RIGHT JOIN:

SELECT customers.name, orders.order_number
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

This statement returns all orders, including those without a corresponding customer.

5. FULL JOIN — Including All Rows from Both Tables

The FULL JOIN retrieves all rows from both tables, including matching and non-matching rows. If no match is found, NULL values are returned for the columns of the non-matching table. To retrieve all customers and all orders, use the following syntax:

SELECT customers.name, orders.order_number
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;

This statement returns all customers and all orders, irrespective of matches.

6. Using Aliases to Simplify Table Names

When joining multiple tables, table names can become lengthy. To simplify the query, you can assign aliases to table names using the AS keyword. For example:

SELECT c.name, o.order_number
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

Here, we assign “c” as an alias for “customers” and “o” as an alias for “orders.”

7. Conclusion

In this lesson, we explored the concept of joining tables in SQL. We covered the INNER JOIN to retrieve data from multiple tables based on a common column. We also discussed the LEFT JOIN, RIGHT JOIN, and FULL JOIN to include all rows from either the left or right table or both. Additionally, we learned how to assign aliases to simplify table names in complex queries. Armed with these techniques, you can effectively query and combine data from multiple tables, leveraging the power of SQL joins.

If you found this course helpful and would like to explore more free courses, I invite you to follow my account on Medium and connect with me on LinkedIn. I regularly share valuable content on these platforms.