What Is the OUTER JOIN in SQL?

Posted on by mtoplistChuyên mục:Top List

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:

Customer_NumberCustomer_NameAgePostal_CodeAddress

103Atelier274400054, RueRoyal

112Signal32830308489 Strong

114Collector273004636 Kilda

119La Roche274400067, rue chimay

121Baane mini324110Ering Shakkes

orders:

Order_NumberOrder_DateExpected_Shipping_DateCustomer_Number

1034531-01-202210-02-2022103

1034630-01-202215-02-2022112

1012005-02-202216-02-2022114

1032506-02-202210-02-2022121

1121108-02-202221-02-20224110

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

SELECT 
  a.Order_Number, 
  b.Customer_Name, 
  b.Postal_Code, 
  b.Address 
FROM orders a 
JOIN customers b  
ON a.Customer_Number = b.Customer_Number;

And here’s the result:

Order_NumberCustomer_NamePostal_CodeAddress

10345Atelier4400054, RueRoyal

10346Signal830308489 Strong

10120Collectors3004636 Kilda

10325Baane Mini4110Ering Shakkes

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:

<SELECT <list of columns>
FROM <table 1> 
LEFT/RIGHT/FULL/INNER JOIN <table 2> 
ON <join condition>
WHERE <other conditions>;

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:


SELECT 
  a.Order_Number, 
  b.Customer_Name, 
  b.Postal_Code 
FROM orders a 
LEFT JOIN customers b -- alternatively use LEFT OUTER JOIN instead of LEFT JOIN
ON a.Customer_Number = b.Customer_Number;

Output:

Order_NumberCustomer_NamePostal_CodeAddress

10345Atelier4400054, RueRoyal

10346Signal830308489 Strong

10120Collectors3004636 Kilda

10325Baane Mini4110Ering Shakkes

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:

SELECT 
  a.Order_Number, 
  b.Customer_Name, 
  b.Postal_Code 
FROM orders a 
RIGHT JOIN customers b -- alternatively use RIGHT OUTER JOIN instead of RIGHT JOIN
ON a.Customer_Number = b.Customer_Number;

Output:

Order_NumberCustomer_NamePostal_Code

10345Atelier44000

10346Signal83030

10120Collectors3004

10325Baane Mini4110

NULLLa Roche44000

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:

SELECT 
  a.Order_Number, 
  b.Customer_Name, 
  b.Postal_Code 
FROM orders a 
FULL JOIN customers b --alternatively use ‘FULL OUTER JOIN’ instead of ‘FULL JOIN’
ON a.Customer_Number = b.Customer_Number;

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!

mtoplist