What is LEFT OUTER JOIN in SQL?

The SQL LEFT JOIN fetches all the records from the left table and only matched records from the right table. For example:

SELECT * from tbl_employees

LEFT JOIN tbl_salaries

ON tbl_employees.col = tbl_ salaries.col;

The left table is the one that is used in the SELECT statement. On the other hand, the right table is specified after the JOIN clause.

The simple JOIN (also known as INNER JOIN) retrieves only matched records from both tables.

In certain situations, you may require getting all rerecords from the table specified in the SELECT statement. In that case, use the LEFT JOIN type in queries as shown in the examples below.

Note: In the case of retrieving all records from the right table, use the RIGHT JOIN. Also, the term “LEFT JOIN” and “LEFT OUTER JOIN” are equal; both produce the same results. In some databases like Oracle, the “LEFT OUTER JOIN” is used.

In MS SQL Server and MySQL databases, both produce the same resultsets.

Syntax for using LEFT JOIN

The syntax of using LEFT JOIN is:

A basic example of using SQL LEFT JOIN

For our examples, I will use two tables in the test database. The sto_employees is used to store the employee’s information (id, name, age, salary etc.) in a department store. Whereas, the sto_orders table keeps the order information like order_id, emp_id, order_date etc.

Let us start by enquiring a few fields from both tables by using a LEFT JOIN:

Query:

SQL LEFT JOIN

The only column from sto_oders table (right table) is the order_date in the above INNER JOIN query. In the graphic table resultset, all the records from sto_employees are returned while the only matched records from sto_oders are displayed.

For unmatched records, the value for order_date is displayed as NULL. These are employees, who never took an order from customers.

The example of using two LEFT JOINs in the single query

Let us combine three tables by using two LEFT JOIN clauses. The purpose is just showing you how to use multiple LEFT JOINs in a single query.

In addition to above two tables, the third table is sto_products which is related to sto_orders table. As such, any product ordered relates to the orders table.

The query with multiple LEFT JOINs:

The resultset with dummy data:

SQL LEFT_JOIN multiple

Using table alias example

Rather than using the table names, you may also use table alias as specifying the tables in the LEFT OUTER JOIN query.

The following query combines two tables by LEFT JOIN using table alias:

The same result is retrieved as in case of the first example.

Sorting the results as using INNER JOIN

For sorting the results in ascending or descending order, you may use the ORDER BY clause. If you use WHERE clause for filtering the results (as shown in next example), then ORDER BY clause is used after this.

If you are using GROUP BY (and/or HAVING clause) then ORDER BY clause is used at the end:

The resultset:

SQL LEFT_JOIN ORDER-BY

You can see, the resultset is sorted by employee names (A-Z) as we specified the emp_name field in the ORDER BY clause. For learning more about the ORDER BY clause, go to its tutorial.

An example of filtering results by WHERE clause in LEFT JOIN

Just like you use the WHERE clause in simple queries with SELECT, you can use the WHERE clause for filtering rows as using the LEFT JOIN in SQL. See a demonstration below:

The result:

SQL LEFT_JOIN WHERE

Using LEFT JOIN with GROUP BY and HAVING clause example

The last example shows using the GROUP BY for returning the aggregated results along with using HAVING clause for filtering the results as using LEFT OUTER JOIN. The query and result is shown below:

The resultset:

SQL LEFT_JOIN GROUP-BY