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 records from the table specified in the SELECT statement. In that case, use the LEFT JOIN type in queries as shown in the examples below.

Syntax for using LEFT JOIN

The syntax of using LEFT JOIN is:

SELECT * FROM left_table

JOIN right_table on left_col = right_col

[WHERE col = value]

[GROUP BY]

[ORDER BY]

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:

SELECT emp_name, emp_age, join_date, order_date 
FROM sto_employees

LEFT JOIN sto_orders  ON sto_employees.id=sto_orders.emp_id;

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 above, 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.

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.

The example of using two LEFT JOINs in a single query

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

In addition to the 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:

SELECT emp_name, emp_age, join_date, order_date, prod_name FROM sto_employees

LEFT JOIN

    sto_orders  ON sto_employees.id=sto_orders.emp_id

LEFT JOIN

    sto_products ON sto_products.prod_id = sto_orders.prod_id;

The resultset with dummy data:

SQL LEFT_JOIN multiple

Using table alias example

Rather than using the table names, you may also use table aliases to specify the tables in the LEFT OUTER JOIN query.

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

SELECT emp_name, emp_age, join_date, order_date 
FROM sto_employees EMPs

LEFT JOIN sto_orders ORDs  ON EMPs.id=ORDs.emp_id;

The same result is retrieved as in the 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 the WHERE clause for filtering the results (as shown in the next example), then ORDER BY clause is used after this.

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

FROM sto_employees EMPs

LEFT JOIN sto_orders ORDs  ON EMPs.id=ORDs.emp_id

ORDER BY emp_name;

The resultset:

SQL LEFT_JOIN ORDER-BY

You can see that 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:

SELECT emp_name, emp_salary, join_date, order_date FROM sto_employees EMPs

LEFT JOIN sto_orders ORDs  ON EMPs.id=ORDs.emp_id

WHERE emp_salary BETWEEN 3500 AND 6000

ORDER BY EMPs.emp_salary;

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 the HAVING clause for filtering the results as using LEFT OUTER JOIN.

The query and result is shown below:

SELECT emp_name AS "Employee Name", SUM(emp_salary) As "Paid Salary"

FROM sto_employees EMPs

LEFT JOIN sto_orders ORDs  ON EMPs.id=ORDs.emp_id

GROUP BY emp_name

HAVING SUM(emp_salary) >=4150;

The resultset:

SQL LEFT_JOIN GROUP-BY

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!