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;
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:
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.
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:
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:
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: