Skip to main content

Joins

Premium

In this lesson, we will introduce the concept of "joins" in SQL and provide an overview of the different types of joins. Joining data from multiple tables is a common task in SQL, and understanding how to perform joins is an essential skill for any SQL user.

Join Diagram

What is a join?

A join in SQL is used to combine data from two or more tables into a single result set. The result set is created by matching the rows from one table with the rows from another table based on a common column, called a "join condition". The join condition specifies how the rows from the two tables are matched.

Basic syntax

The syntax of a join in SQL typically follows this pattern:

SQL
SELECT columns 
FROM table_1 
JOIN table_2 
ON join_condition;

The JOIN keyword is used to specify the type of join to be performed, and the ON keyword is used to specify the join condition. The join condition is an expression that evaluates to TRUE or FALSE for each row in the tables being joined, and determines which rows from each table are included in the result set.

Typically speaking, when two tables are joined, the join condition is based on a foreign key in one table matching the primary key of the other table, as we'll see in the examples later.

Why do we need to join data?

Joining data across tables allows us to combine information from multiple tables in a meaningful way. In many cases, data is stored in separate tables for organizational purposes, such as to separate data for different departments, to enforce data integrity, or to improve query performance. Joining data from multiple tables allows us to bring together related information from these separate tables into a single result set.

When you join two tables in SQL, the result table will contain all columns from both tables. The columns from each table are distinguished by their table name, which is specified in the query using the format table_name.column_name.

Here's an example of a simple join between two tables, customers and orders, where the join condition is based on a foreign key in the orders table that refers to the primary key in the customers table:

SQL
SELECT customers.id, customers.name, orders.order_id, orders.total
FROM customers
JOIN orders
ON customers.id = orders.customer_id;

The result of this query would be a table with all columns from both tables, as shown below:

id  | name      | order_id | total  
----|-----------|----------|-------  
1   | John Doe  | 1001     | 100.00  
1   | John Doe  | 1002     | 200.00  
2   | Jane Doe  | 1003     | 150.00  
3   | John Smith| 1004     | 75.00

Types of Joins

There are several types of joins in SQL, each with a different purpose and behavior. The most common types of joins are:

  • Inner Join: An inner join returns only the rows where there is a match in both tables. The result set contains only the rows where the join condition is true.

  • Left Join: A left join returns all the rows from the left table and the matching rows from the right table. If there is no match in the right table, the result set will contain a NULL value in the columns from the right table.

  • Right Join: A right join returns all the rows from the right table and the matching rows from the left table. If there is no match in the left table, the result set will contain a NULL value in the columns from the left table.

  • Full Outer Join: A full outer join returns all the rows from both tables, whether there is a match or not. If there is no match in either table, the result set will contain NULL values in the columns from the missing table.

  • Cross Join: A cross join returns the Cartesian product of the two tables, i.e., it returns every possible combination of rows from both tables.

Next Lesson