Home » Oracle SQL » Oracle Left Join with Examples

Oracle Left Join with Examples

In this tutorial, we will learn how to use LEFT JOIN or LEFT OUTER JOIN to join more than two tables.

Generally, Joins are used for returning matching rows in both tables which satisfy the join condition. In some cases, we may want to keep all rows from either left or right table and for this reason, we use Outer Joins.

LEFT JOIN or LEFT OUTER JOIN returns all rows from left table and only the matching rows from right table.

Syntax:

The syntax of LEFT JOIN is as follows:

SELECT column_names
FROM Table_1
LEFT JOIN Table_2 ON
    join_condition;

Here,

Table 1 and Table 2 – Tables that we want to join. We assume Table_1 to be the left table and Table_2 to be right.

join_condition – The join condition which is based on the common column between both the tables.

The Left Join query returns all the rows from the left table and only the matching rows from right table.

The Venn Diagram For Left Join is as follows:

Oracle Left Join Examples

1. Oracle Left Join Example

We, use LEFT JOIN to join Employees table with Departments table in this query:

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

Result:

The query returns all rows from the left ( Employees) table and only the matching rows from Right ( Departments) table.

2. Left Join with Multiple Tables

In this query, we use the LEFT OUTER JOIN for joining more than 2 tables:

SELECT first_name, last_name,city,
       salary,department_name 
FROM employees e
LEFT JOIN departments d 
ON e.department_id= d.department_id
LEFT JOIN locations l
ON d.location_id=l.location_id;

Results:

The query joins the first 2 tables first in which the Employees table is the left table and then it joins it with the third table, which is Locations table. In the final result set, all rows from Employees table are present and only matching rows from subsequent tables are present in the results.

3. Left Join with Using Clause

We can also use LEFT JOIN with USING clause instead of using ON. The syntax for usage of USING clause is as follows:

SELECT
    column_names
FROM
    Table_1
LEFT JOIN Table_2 USING(c1,c2,c3, ...);

Here,

c1, c2, c3 etc – These are column names which are common in both tables.

Let’s see an example:

SELECT first_name, last_name, 
       salary,department_name 
FROM employees 
LEFT JOIN departments USING department_id;

Result:

The query uses LEFT JOIN with USING clause to join Employees table with Departments table. It shows the salary and department name of each employee working within the organization.

4. Additional conditions using AND vs WHERE Clause

WHERE

The following query returns the list of all employees with their department names who are earning greater than 10000.

SELECT first_name, last_name, 
       salary,department_name 
FROM employees e
LEFT OUTER JOIN departments d 
ON e.department_id=d.department_id
WHERE salary > 10000;

Result:

The query returns a list of 15 employees earning more than 10000 with their department names.

Same condition with AND:

Let’s see the same query, but this time we will using AND instead of WHERE with the Join condition:

SELECT first_name, last_name, 
       salary,department_name 
FROM employees e
LEFT OUTER JOIN departments d 
ON e.department_id=d.department_id
AND salary > 10000;

Result:

As, we can see the output is NOT same.

The query returns all rows(107) from Employee table and matching rows from Department table. This happens because we used AND instead of WHERE on the ON condition.

In this tutorial, we learned how to use LEFT OUTER JOIN in different scenarios to understand its usage.

<< Previous
INNER JOIN

Next >>
RIGHT JOIN