What Is the OUTER JOIN in SQL?

Even if you are new to SQL, you must have come across the term OUTER JOIN. In this article, I will explain what OUTER JOIN in SQL does. I’ll use some practical examples to demonstrate how it is used in day-to-day SQL applications.

If you want to use SQL for practical purposes, learning the different JOINs is extremely important. In fact, you will be using a JOIN in just about every second query you write. Therefore, it’s imperative that you spend some effort getting comfortable with them.

While this article includes some basic information about JOIN in general and OUTER JOIN in particular, I recommend serious learners take the SQL JOINs course by LearnSQL.com. It’s an interactive group of 93 practical exercises divided over 5 sections, and it will give you an in-depth understanding of SQL JOINs.

Having said that, let’s dive straight into the topic, starting with what a SQL JOIN does.

What Is a JOIN?

A SQL JOIN is used to combine data from two or more tables and then generate a single output table that contains selected columns from both tables. You will usually use one or more common values in the tables to link them. You specify how to combine rows from two tables in the JOIN condition: the rows from both tables that satisfy the condition are combined and added to the result table.

For instance, say you have a database of customer information. When a customer registers with your business. you save their details (Customer_Number, Customer_Name, Age, Postal_Code, and Address) in a table called customers. The details of the orders that the customer places are stored in another table called orders, which contains the Order_Number, Order_Date, Expected_Shipping_Date, and Customer_Number.

Imagine that a customer places an order and you need to know its delivery address. However the table orders only has the Customer_Number. To retrieve the customer address, you will need to JOIN the tables customers and orders based on the Customer_Number.

In this case, Customer_Number serves as the column values you compare.

customers:

orders:

Here’s the query you’d use to link all order numbers with customer names and addresses:

And here’s the result:

In this query, SQL selects the columns Order_Number from the table orders and Customer_Name, Postal_Code, and Address from the table customers. Customer_Number is the column used in the JOIN condition. For each order, the corresponding customer number from orders is compared to the customer number in the customers table. The customer name, postal code and address is retrieved for that order.

This is a classic example of an INNER JOIN (also known as a plain or regular JOIN; the INNER keyword is optional). It’s one of the most commonly used JOINs in SQL. INNER JOIN basically means that only those rows where the values are common between the two tables will be retrieved. Notice that rows with the customer number 119 (which had no corresponding orders) and order number 11211 (which had no corresponding customer number) were not included in the result.

However, INNER JOIN is not the only JOIN that SQL offers. There are several types of OUTER JOIN you should know.

OUTER JOINS Explained

In SQL, JOINs are categorized as:

1. INNER JOIN - Returns only rows where the values match the JOIN condition in both tables. Rows in either table that don’t match this condition are ignored.
2. OUTER JOIN
   1. LEFT JOIN - Returns all rows from the left table (the table before the JOIN keyword). For rows that have a match in the right table, it returns the values from the right table; for rows without a match in the right table, it fills the missing values with NULLs.
   2. RIGHT JOIN - Returns all rows from the right table (the table after the JOIN keyword). For rows that have a match in the left table, it returns the values from the left table; for rows without a match in the left table, it fills the missing values with NULLs.
   3. FULL JOIN - Returns all rows in both tables, using NULLs for values with no match.

For any JOIN, the basic SQL query structure is:

Note: Instead of the keyword LEFT JOIN you can alternatively use LEFT OUTER JOIN. The keyword OUTER is optional. Similarly, you can use RIGHT OUTER JOIN instead of RIGHT JOIN as well as FULL OUTER JOIN instead of FULL JOIN without any change in the query results.

• I will keep the scope of this article limited to OUTER JOINs, but feel free to read more details on types of JOINs.

OUTER JOIN is used to retrieve all records from tables, even for those records with no matching value in the other table based on the JOIN condition. In such cases, it returns NULL as the value for the missing columns.

• I will keep the scope of this article limited to OUTER JOINs, but feel free to read more details on types of JOINs.

OUTER JOIN is used to retrieve all records from tables, even for those records with no matching value in the other table based on the JOIN condition. In such cases, it returns NULL as the value for the missing columns.

As mentioned previously, there are three types of OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN. Let me take you through some examples to demonstrate how they work.

LEFT OUTER JOIN

Imagine you need to retrieve all the orders along with customer information. But you also need to include those orders which do not have customer information. In this case, you can use a LEFT OUTER JOIN.

Query:

Output:

Notice that since you specified ‘LEFT JOIN’ in the query, this query works by first retrieving all the rows in the left table and then retrieving the corresponding value in the right table. The left table is the table given before the JOIN keyword (here, the orders table). The query returns NULL in the columns from the right table (Customer_Name, Postal_Code) for all those rows for which there’s no matching Customer_Number value in the customers table. Note that order number 11211 (the order without a customer assigned) has been included in the results, with NULL values in the Customer_Name and Postal_Code columns.

RIGHT OUTER JOIN

A RIGHT OUTER JOIN works exactly opposite to a LEFT OUTER JOIN. It retrieves all rows from the right table (the table given after the JOIN keyword) and the corresponding values from the left table. There are NULLs for all the rows for which the left table does not have a match. Let’s see an example.

Query:

Output:

Here, the customer (La Roche) who hasn’t placed any orders is in the query results; the order without a customer has been omitted. This is because we used a RIGHT OUTER JOIN; all records from the right (customers) table have been included.

FULL OUTER JOIN

But what if you want all the rows from both tables? Use the FULL OUTER JOIN. This JOIN returns all the rows from both tables, substituting NULL for any row value that is not present in the other table.

Query:

Output:

The results include both the customer La Roche (who has no orders) and order 11211 (which has no customer information).

Difference Between INNER and OUTER JOIN

As you might have figured out, the key difference between INNER JOIN and OUTER JOIN is whether to include the rows for which we don’t have a match in the other table. While an INNER JOIN only returns rows where there is a match between the two tables, an OUTER JOIN (depending on the type) also returns those rows for which there is no matching row in the other table.

Ready to Use OUTER JOIN in Your Queries?

I hope this article has given you a good idea of how to use OUTER JOIN. If you want to further dive into the usage of SQL JOINs, check out the SQL JOINs course I mentioned earlier. If you are just starting your SQL learning journey, I would recommend the SQL from A to Z track. It contains 7 SQL courses that lead you from basic to advanced SQL. It’s great for developing a strong foundation in this language.

Either way, much like any other field, continuous learning is the key to success. So, all the best and happy learning!