Difference Between Left Join and Left Outer Join in SQL

SQL joins are used to combine data from multiple tables in relational databases. When working with relational databases, the terms left join and left outer join are often used interchangeably. Do they mean the same thing, or is there a difference between them? In this article, we will compare left join vs. left outer join, breaking down their syntax, behavior, and when to use them.

What is left join?

Left join in SQL retrieves records from two tables based on a specific column in each table.

• It includes all records from the left table, even if no matching records exist in the right table.
• If a row in the left table has no corresponding match in the right table, the right table’s columns have null values for the particular rows.
• All the rows from the right table that do not have matching rows in the left table are excluded from the output.

The syntax for left join is as follows:

SELECT column_names 
FROM left_table 
LEFT JOIN right_table 
ON left_table.column_name = right_table.column_name; 

In this syntax,

• column_names represents the columns that we want in the output.
• left_table and right_table are database tables we join using the left join operation.
• left_table.column_name and right_table.column_name are the column names we use to match the rows in left_table and right_table.

To understand the left join, let’s look at an example.

Left join example

Suppose we have a Customers table that contains the customer_id, name, and city for customers at a retail store.

+-------------+---------+----------+ 
| customer_id | name    | city     | 
+-------------+---------+----------+ 
|           1 | Alice   | New York | 
|           2 | Bob     | Chicago  | 
|           3 | Charlie | Miami    | 
|           4 | David   | Dallas   | 
+-------------+---------+----------+ 

We also have an Orders table that contains order_id, cust_id, and product for the orders.

+----------+---------+------------+ 
| order_id | cust_id | product    | 
+----------+---------+------------+ 
|      101 |       1 | Laptop     | 
|      102 |       2 | Smartphone | 
|      103 |       1 | Headphones | 
|      104 |       5 | Earphones  | 
+----------+---------+------------+ 

Now, if we want the customer_id, name, city, and product for the customers, we can use left join on the Customers and Orders table, as shown in the following SQL statement:

SELECT customer_id, name, city, product  
FROM Customers LEFT JOIN Orders  
ON Customers.customer_id = Orders.cust_id; 

After executing this statement, we get the following output:

+-------------+---------+----------+------------+ 
| customer_id | name    | city     | product    | 
+-------------+---------+----------+------------+ 
|           1 | Alice   | New York | Headphones | 
|           1 | Alice   | New York | Laptop     | 
|           2 | Bob     | Chicago  | Smartphone | 
|           3 | Charlie | Miami    | NULL       | 
|           4 | David   | Dallas   | NULL       | 
+-------------+---------+----------+------------+ 

You can observe that all the rows from the Customers table are included in the output table. For the customer ID 3 and 4, we don’t have any data in the Orders table. Due to this, the city and product columns in the output table for these customer IDs contain NULL, as these columns are fetched from the Orders table. Also, there is no matching customer for the customer id 5 from the Orders table. Hence, this row from the Orders table is excluded from the output.

Having discussed left join, let’s discuss left outer join with an example so that we can compare these operations.

What is left outer join?

The left outer join in SQL is the same as the left join. It retrieves all the records from the left table and the matching records from the right table. If no match is found in the right table, NULL values are returned for the right table’s columns.

The syntax for the left outer join is as follows:

SELECT column_names 
FROM left_table 
LEFT OUTER JOIN right_table 
ON left_table.column_name = right_table.column_name; 

You can observe that the syntax for the left outer join is the same as that for the left join with an additional OUTER keyword. However, this difference in the syntax has no effect on how the statement works. When executed, the left outer join statement gives the same output as the left join statement.

Left outer join example

To see how left outer join works, let’s again fetch the customer_id, name, city, and product from the Customers and the Orders table using LEFT OUTER JOIN.

SELECT customer_id, name, city, product  
FROM Customers LEFT OUTER JOIN Orders  
ON Customers.customer_id = Orders.cust_id; 

If we execute this statement, we get the following output.

+-------------+---------+----------+------------+ 
| customer_id | name    | city     | product    | 
+-------------+---------+----------+------------+ 
|           1 | Alice   | New York | Headphones | 
|           1 | Alice   | New York | Laptop     | 
|           2 | Bob     | Chicago  | Smartphone | 
|           3 | Charlie | Miami    | NULL       | 
|           4 | David   | Dallas   | NULL       | 
+-------------+---------+----------+------------+ 

You can see that the output from the left outer join operation is the same as the output from the left join operation.

Left join vs left outer join: similarities and differences

The similarities and differences between the left join and left outer join are as follows:

Conclusion

In SQL, the left join and the left outer join are the same. There is no functional or performance difference between them. The keyword OUTER is optional and does not affect query execution. You can use either of these operations to fetch data from two tables when you need all the rows from one table and the matching rows from the other.

For a deeper understanding of SQL, explore the learn SQL course, which covers fundamental concepts like queries, joins, and data manipulation. To apply SQL in data analysis, explore the analyze data with SQL skill path, which provides hands-on experience with real-world datasets for efficient data extraction and analysis.

Happy learning!