What’s the Difference Between LEFT OUTER JOIN And LEFT JOIN?

In SQL, there is no functional difference between LEFT OUTER JOIN and LEFT JOIN. They are two different ways to specify the same type of join operation. Both LEFT OUTER JOIN and LEFT JOIN perform the same task of combining rows from two or more tables based on a specified condition and including unmatched rows from the left table.

The keywords LEFT OUTER JOIN and LEFT JOIN can be used interchangeably in most database management systems. They produce the same result set by including all rows from the left table, regardless of whether they have matching rows in the right table. If there is a match, the corresponding rows from the right table are included in the result set. If there is no match, the result will contain NULL values for the columns of the right table.

The choice of using LEFT OUTER JOIN or LEFT JOIN typically depends on personal preference or the coding style guidelines of your organization. Some database developers may prefer using LEFT JOIN as it is shorter and more concise, while others may prefer using LEFT OUTER JOIN to emphasize the intention of performing an outer join.

In summary, LEFT OUTER JOIN and LEFT JOIN are synonymous in SQL, and you can use either of them to achieve the same result of retrieving data from the left table and including any matching rows from the right table, along with NULL values for non-matching rows.

LEFT [OUTER] JOIN Syntax

The syntax for a left outer join in SQL is as follows:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

In this syntax, table1 and table2 are the names of the tables you want to join. column_name(s) represents the column or columns you want to select from the tables. The ON clause specifies the condition for the join, typically matching the columns in both tables.

The keyword LEFT indicates that it is a left outer join. This type of join will include all the rows from the left table (table1 in this case) and the matching rows from the right table (table2). If there is no match, the result will contain NULL values for the columns from the right table.

Feel free to adjust the table names, column names, and join conditions to match your specific use case.

For example, the following query would return all customers, even if they haven't placed any orders:

SELECT *
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This query would return all rows from the Customers table, even if there are no matching rows in the Orders table. The unmatched rows in the Customers table would have NULL values in the OrdersID column.

Here is an example of the output of the above query:

CustomerID | Name | OrdersID
----------+-------+---------
1          | John Doe | NULL
2          | Jane Doe | 100
3          | Mary Smith | 200
4          | Peter Jones | 300

As you can see, the unmatched rows in the Customers table have NULL values in the OrdersID column. This indicates that these customers have not placed any orders.

I hope this helps! Let me know if you have any other questions.

Examples of LEFT OUTER JOIN

The first table, departments, has the following data:

iddepartment_name
1Accounting
2Sales
5Compliance

The second table is employees, and you can create it using this query. The table has the following data:

Two employees have a NULL value in the column department_id. These are new employee records that haven’t been updated with the department yet.

Example - Find All Employees and Their Departments

This is an article about LEFT OUTER JOIN, so there’s no surprise: we’ll use exactly that join to solve this problem. Here’s the query:

SELECT e.id,
     e.first_name,
     e.last_name,
     d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id
ORDER BY e.id;

The ID numbers, names, and departments of the workers are the outcome data that we require. These columns are in SELECT because of this. We must access the data from both tables in order to acquire all of these columns. Let's look at the procedure.

The table workers are first mentioned in FROM. Why did you choose this table over the other? Keep in mind that in an LEFT JOIN, it matters which table is referred first. Why? Given that it is the left table, a LEFT JOIN will always return all of the data from that table.

Because we need to list every employees from that database, we start by referring to the table's workers. The table departments are then referred to. If the column department_id from the table workers matches the column id from the table departments, we link the two tables. These two columns are perfect for usage in the join condition since they are common dimensions (the primary key and foreign key) between these two tables.

We group the outcomes according to employee ID to make the output easier to read:

The output lists all 15 employees and their departments, which are Accounting and Sales. Some employees have NULL values in the department_name column. Two of these employees are new employees without an updated department ID in the table. These had NULL values in the initial employee table, and are marked in blue.

Other employees, marked in green, also have NULL values. This is because of the LEFT JOIN. All these employees have a department ID of 4, but the table departments do not contain this value. It seems that the table departments might also need to be updated. A really shabby database for such a respectable fictive company!

Remember: Values from the left table that are not found in the right table will be shown as NULL.

What If We Used an INNER JOIN?

Let’s write the above query again, this time with an INNER JOIN (usually abbreviated to just JOIN) instead of a LEFT JOIN:

SELECT e.id,
     e.first_name,
     e.last_name,
     d.department_name
FROM departments d
JOIN employees e
ON e.department_id = d.id
ORDER BY e.id;

The syntax is exactly the same; we just used a different join type. Let’s see what the result will be:

Sure, here is the text with the same meaning:

The output shows only nine employees, but there are actually 15 employees in the database. The missing employees are those with IDs 3, 4, 7, 9, 11, and 12. These employees are not included in the result because they have NULL values in the department_name column.

INNER JOIN will only return rows that match both tables. This means that the employees with NULL values in the department_name column are not included in the result, because they do not have a matching row in the departments table.

LEFT JOIN, on the other hand, will return all rows from the left table, even if there are no matches in the right table. This is why the missing employees are included in the LEFT JOIN output.

In this situation, where we wanted to see all employees and their departments, the LEFT JOIN is the right choice. This is because it allows us to see the employees who do not have a department, which can help us to identify any problems with our database.

Beginner's Guide to Writing JOINs in SQL

-- Cascading LEFT JOINs
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
LEFT JOIN table3 ON table2.column = table3.column;

-- Combining LEFT JOIN and INNER JOIN
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
INNER JOIN table3 ON table2.column = table3.column;