FB

An SQL join is a concept that allows you to retrieve data from two or more tables in a single query.

It’s what makes databases so useful, and allows for data to be stored in separate tables and combined when it is needed.

Let’s take a look at what SQL joins are, how to use them, and see some examples.

What is a join?

A join is a way to look at data in two different tables.Oracle SQL Joins

In SQL, you often need to write queries that get data from two or more tables. Anything but the simplest of queries will usually need data from two tables, and to get this data, you need to use a join.

Joins are a standard concept in SQL and have special keywords that you can use. Oracle also has some enhancements to this, which I’ll also explain later in this guide.

 

Our Example Data

Before we get started, let’s look at some sample data.

To help explain joins, it helps to see how the SQL queries would work and what the results would look like.

So, let’s use an employee and department example. It’s a common example that you might be used to.

This is the department table:

DEPARTMENT_ID DEPARTMENT_NAME
1 Executive
2 HR
3 Sales
4 Development
5 Support
6 Research

And this is the employee table.

EMPLOYEE_
ID
FULL_NAME DEPARTMENT_
ID
ROLE MANAGER_
ID
1 John Smith 1 CEO (null)
2 Sarah Goodes 1 CFO 1
3 Wayne Ablett 1 CIO 1
4 Michelle Carey 2 HR Manager 1
5 Chris Matthews 3 Sales Manager 2
6 Andrew Judd 4 Development Manager 3
7 Danielle McLeod 5 Support Manager 3
8 Matthew Swan 2 HR Representative 4
9 Stephanie Richardson 2 Salesperson 5
10 Tony Grant 3 Salesperson 5
11 Jenna Lockett 4 Front-End Developer 6
12 Michael Dunstall 4 Back-End Developer 6
13 Jane Voss 4 Back-End Developer 6
14 Anthony Hird (null) Support 7
15 Natalie Rocca 5 Support 7

This shows a list of 6 departments and 16 employees. The manager_id column in the employee table refers to the employee_id column in the same table. I’ll show you how to use this later in this guide.

If you want to follow along and create the database yourself, here is the SQL:

CREATE TABLE department (

department_id NUMBER(5) PRIMARY KEY,

department_name VARCHAR2(100)

);

 

CREATE TABLE employee (

employee_id NUMBER(5) PRIMARY KEY,

full_name VARCHAR2(100),

department_id NUMBER(5) REFERENCES department(department_id),

job_role VARCHAR2(100),

manager_id NUMBER(5)

);

 

INSERT INTO department(department_id, department_name) VALUES (1, ‘Executive’);

INSERT INTO department(department_id, department_name) VALUES (2, ‘HR’);

INSERT INTO department(department_id, department_name) VALUES (3, ‘Sales’);

INSERT INTO department(department_id, department_name) VALUES (4, ‘Development’);

INSERT INTO department(department_id, department_name) VALUES (5, ‘Support’);

INSERT INTO department(department_id, department_name) VALUES (6, ‘Research’);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (1, ‘John Smith’, 1, ‘CEO’, NULL);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (2, ‘Sarah Goodes’, 1, ‘CFO’, 1);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (3, ‘Wayne Ablett’, 1, ‘CIO’, 1);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (4, ‘Michelle Carey’, 2, ‘HR Manager’, 1);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (5, ‘Chris Matthews’, 3, ‘Sales Manager ‘, 2);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (6, ‘Andrew Judd’, 4, ‘Development Manager’, 3);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (7, ‘Danielle McLeod’, 5, ‘Support Manager’, 3);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (8, ‘Matthew Swan’, 2, ‘HR Representative’, 4);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (9, ‘Stephanie Richardson’, 2, ‘Salesperson’, 5);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (10, ‘Tony Grant’, 3, ‘Salesperson’, 5);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (11, ‘Jenna Lockett’, 4, ‘Front-End Developer’, 6);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (12, ‘Michael Dunstall’, 4, ‘Back-End Developer’, 6);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (13, ‘Jane Voss’, 4, ‘Back-End Developer’, 6);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (14, ‘Anthony Hird’, null, ‘Support’, 7);

INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)

VALUES (15, ‘Natalie Rocca’, 5, ‘Support’, 7);

Download Free Guide: "6 Examples of Joins"

 

Why Use Joins?

The main benefit of storing data in a database is that you can use multiple tables, where each table stores information about a specific type of data.

Using our example, all department information is stored in one table, and all employee information is stored in another table.

There are references in one table to the other table: for example, to see which department each employee works in.

Storing data in separate tables is the main concept of database normalisation and ensures your database is efficient and well-designed.

But, if data is in separate tables, how can we retrieve the data from more than one table?

We can run a SELECT query to find employees, and another SELECT to find departments, and use loops in our code to match the data.

But that’s very inefficient.

SQL allows you to write a single query to retrieve data from two (or more) tables.

This is using the concept of a join.

You select data from one table, which is joined to another table that you can also select from.

This way, you can get the data you need from any table in the database, as long as you join it in your query.

 

Creating a Join

We have our two tables now. How do we create a join?

A join isn’t an object we create on the database. It’s something we add to our query using some specific keywords.

You need at least two tables to create a join – one will join to the other.

If you want more tables in your query, you can join those as well. It works kind of like sections of a pipe. You can join two tables (pipes) together, and to join a third, you just connect the third table (or pipe) to either one of the first two.

To join two tables together, you need to have a column to join them on. This is because you need a way of having a row in one table linking to a row in another table.

Using our example, how do we know what department an employee is in? There is a department_id column in the employee table. This is linked to the department table. And this is how we join the two tables together.

It’s usually done using a primay key/foreign key field relationship.

The syntax for a join is:

SELECT columns

FROM table1

JOIN_TYPE table2 ON table1.column1 = table2.column1;

The JOIN_TYPE can be one of many different join types. You replace the word JOIN_TYPE here with the type of join you want.

 

Where Can You Use Joins?

Joins are most often used in SELECT statements.

But, you can use joins in several places:

  • SELECT query
  • Subquery of an UPDATE query
  • Subquery of an INSERT query
  • Subquery of a DELETE query

In terms of the objects you can use them on, you can join to:

  • Tables
  • Views
  • Materialised views.

You can also mix and match these types. You can join a table to a view, and a view to a materialised view, and so on. It’s all the same to Oracle.

 

What are the different types of joins?

So, what different types of joins can you perform in Oracle SQL? They are:

  • Inner join
  • Left outer join/Left join
  • Right outer join/Right join
  • Full outer join/full join
  • Cross join/cartesian product

You can also perform a “self join”, and use equijoins and non-equijoins, which I’ll explain later in the guide.

 

Inner Join

An inner join is a standard type of join. It tells Oracle you want to see all records in table1 and table2 where there is a record in both tables.

This Venn diagram shows you how the join is represented.

Inner Join Venn Diagram

Table 1 & Table 2

For the data to be shown, there needs to be a record in both tables.Table1 and table2 are the two circles, and the coloured section is what is returned by the join. In this case, it’s the set of data in the middle that exists in both tables.

If there is a record in table1 but not table2, then the data will not be shown. It’s the same the other way – if there is a record in table2 but not table1, the data will not be shown.

Here is the way that the data is loaded:

  • For each record in table1
  • Look for a match in table2
  • Match found?
    • Show data from table1 and table2
  • No match found?
    • No data shown

To write this join in SQL, you can use the INNER JOIN keyword, or the JOIN keyword. The INNER is optional.

Using our employee and department tables, an inner join could look like:

SELECT e.full_name, e.job_role, d.department_name

FROM employee e

INNER JOIN department d ON e.department_id = d.department_id;

You don’t need the INNER word, so this will return the same result:

SELECT e.full_name, e. job_role, d.department_name

FROM employee e

JOIN department d ON e.department_id = d.department_id;

This query says “show me the full name, role, and department name, where the department_id matches in both tables”.

Let’s see the output.

FULL_NAME ROLE DEPARTMENT_NAME
John Smith CEO Executive
Sarah Goodes CFO Executive
Wayne Ablett CIO Executive
Michelle Carey HR Manager HR
Chris Matthews Sales Manager Sales
Andrew Judd Development Manager Development
Danielle McLeod Support Manager Support
Matthew Swan HR Representative HR
Stephanie Richardson Salesperson HR
Tony Grant Salesperson Sales
Jenna Lockett Front-End Developer Development
Michael Dunstall Back-End Developer Development
Jane Voss Back-End Developer Development
Natalie Rocca Support Support

You can see that it has:

  • Shown all employee records that have a department.
  • Shown all department records that match the employee records
  • Excluded department ID 6 (Research), because there is no employees in that department.
  • Excluded employee ID 14 as they have no department_id

We can also represent this data side by side:

EMPLOYEE _
ID
FULL_NAME DEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1 John Smith 1 1 Executive
2 Sarah Goodes 1 2 HR
3 Wayne Ablett 1 3 Sales
4 Michelle Carey 2 4 Development
5 Chris Matthews 3 5 Support
6 Andrew Judd 4 6 Research
7 Danielle McLeod 5
8 Matthew Swan 2
9 Stephanie Richardson 2
10 Tony Grant 3
11 Jenna Lockett 4
12 Michael Dunstall 4
13 Jane Voss 4
14 Anthony Hird (null)
15 Natalie Rocca 5

The grey rows are those that are not shown by the query.

So, that’s the inner join. It’s probably the most common type of join that you would use.

 

Left Outer Join

The next type of join that we’ll look at is the Left Outer Join. It’s also referred to as a Left Join, because the OUTER keyword is optional.

This join will show you all data from the first table. If there is a matching record in the second table, it will show that, otherwise it shows NULL.

This Venn diagram demonstrates what a Left Outer Join shows.

Left Outer Join Venn Diagram

Table 1 & Table 2

It’s called a LEFT join because it shows all data from the table on the left of the keyword.

Here is the way that the data is loaded:

  • For each record in table1
  • Show the data from table1
  • Look for a match in table2
    • Match found?
      • Show data from table2
    • Match not found?
      • Show NULL

The main difference between this join and the inner join is that everything in table1 is shown.

Let’s look at the SQL for a left outer join.

SELECT e.employee_id, e.full_name, e.job_role, d.department_id, d.department_name

FROM employee e

LEFT OUTER JOIN department d ON e.department_id = d.department_id;

These are the results for this query:

EMPLOYEE_
ID
FULL_NAME JOB_ROLE DEPARTMENT_
ID
DEPARTMENT_
NAME
3 Wayne Ablett CIO 1 Executive
2 Sarah Goodes CFO 1 Executive
1 John Smith CEO 1 Executive
9 Stephanie Richardson Salesperson 2 HR
8 Matthew Swan HR Representative 2 HR
4 Michelle Carey HR Manager 2 HR
10 Tony Grant Salesperson 3 Sales
5 Chris Matthews Sales Manager 3 Sales
13 Jane Voss Back-End Developer 4 Development
12 Michael Dunstall Back-End Developer 4 Development
11 Jenna Lockett Front-End Developer 4 Development
6 Andrew Judd Development Manager 4 Development
15 Natalie Rocca Support 5 Support
7 Danielle McLeod Support Manager 5 Support
14 Anthony Hird Support (null) (null)

The LEFT JOIN is the same, as the OUTER keyword is optional.

SELECT e.employee_id, e.full_name, e.job_role,

d.department_id, d.department_name

FROM employee e

LEFT JOIN department d ON e.department_id = d.department_id;

We can also represent this data side by side:

EMPLOYEE _
ID
FULL_NAME DEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1 John Smith 1 1 Executive
2 Sarah Goodes 1 2 HR
3 Wayne Ablett 1 3 Sales
4 Michelle Carey 2 4 Development
5 Chris Matthews 3 5 Support
6 Andrew Judd 4 6 Research
7 Danielle McLeod 5
8 Matthew Swan 2
9 Stephanie Richardson 2
10 Tony Grant 3
11 Jenna Lockett 4
12 Michael Dunstall 4
13 Jane Voss 4
14 Anthony Hird (null)
15 Natalie Rocca 5

The grey rows are those that are not shown by the query.

 

Right Outer Join

The right outer join, or right join, is another type of outer join. It’s very similar to the left join.

The only difference is that the tables are switched around.

With the right join, the table on the right (or the table that is specified second in the join) has all of its rows displayed. If there is a match in the left table, then show that record, otherwise show a NULL value.

This Venn diagram demonstrates what a Right Outer Join shows.

Right Outer Join Venn Diagram

Table 1 & Table 2

It’s called a Right join because it shows all data from the table on the right of the keyword.

Here is the way that the data is loaded:

  • For each record in table2
  • Show the data from table2
  • Look for a match in table1
    • Match found?
      • Show data from table1
    • Match not found?
      • Show NULL

Let’s take a look at some sample SQL:

SELECT e.employee_id, e.full_name, e.job_role,

d.department_id, d.department_name

FROM employee e

RIGHT OUTER JOIN department d ON e.department_id = d.department_id;

Results:

EMPLOYEE_
ID
FULL_NAME JOB_ROLE DEPARTMENT_
ID
DEPARTMENT_
NAME
1 John Smith CEO 1 Executive
2 Sarah Goodes CFO 1 Executive
3 Wayne Ablett CIO 1 Executive
4 Michelle Carey HR Manager 2 HR
5 Chris Matthews Sales Manager 3 Sales
6 Andrew Judd Development Manager 4 Development
7 Danielle McLeod Support Manager 5 Support
8 Matthew Swan HR Representative 2 HR
9 Stephanie Richardson Salesperson 2 HR
10 Tony Grant Salesperson 3 Sales
11 Jenna Lockett Front-End Developer 4 Development
12 Michael Dunstall Back-End Developer 4 Development
13 Jane Voss Back-End Developer 4 Development
15 Natalie Rocca Support 5 Support

You can see that the results are similar to the left join. However, the department of Research is shown, even though it has no employees. The employee values shown are NULL.

Also, because the OUTER keyword is optional, this query will also work:

SELECT e.employee_id, e.full_name, e.job_role,

d.department_id, d.department_name

FROM employee e

RIGHT JOIN department d ON e.department_id = d.department_id;

You can change a Left Join to a Right Join by swapping the join types and the tables.

For example, these two queries will give the same results:

Left Join

SELECT e.employee_id, e.full_name, e.job_role,

d.department_id, d.department_name

FROM employee e

LEFT OUTER JOIN department d ON e.department_id = d.department_id;

Right Join

SELECT e.employee_id, e.full_name, e.job_role,

d.department_id, d.department_name

FROM department d

RIGHT OUTER JOIN employee e ON e.department_id = d.department_id;

We can also represent this data side by side:

EMPLOYEE _
ID
FULL_NAME DEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1 John Smith 1 1 Executive
2 Sarah Goodes 1 2 HR
3 Wayne Ablett 1 3 Sales
4 Michelle Carey 2 4 Development
5 Chris Matthews 3 5 Support
6 Andrew Judd 4 6 Research
7 Danielle McLeod 5
8 Matthew Swan 2
9 Stephanie Richardson 2
10 Tony Grant 3
11 Jenna Lockett 4
12 Michael Dunstall 4
13 Jane Voss 4
14 Anthony Hird (null)
15 Natalie Rocca 5

The grey rows are those that are not shown by the query.

 

Full Outer Join

The final type of outer join is the Full Outer Join, or Full Join.

This join type will get all of the rows from both tables. If a match is found, then it displays the matching records. If not, it displays NULL values.

It’s like a Left Join or a Right Join, but it looks for matches both ways and displays NULL values in either set of data.

It’s the combination of a Left Join and a Right Join.

Once again, the OUTER keyword is optional.

This diagram shows what a Full Join looks like:

Full Outer Join Venn Diagram

Table 1 & Table 2

Here’s how the data is loaded:

  • For each record in table1
  • Show the data from table1
  • Look for a match in table2
    • Match found?
      • Show data from table2
    • Match not found?
      • Show NULL
  • For each unmatched record in table2
    • Show the data from table2

Let’s take a look at some sample SQL:

SELECT e.employee_id, e.full_name, e.job_role,

d.department_id, d.department_name

FROM employee e

FULL OUTER JOIN department d ON e.department_id = d.department_id;

Results:

EMPLOYEE_
ID
FULL_NAME JOB_ROLE DEPARTMENT_
ID
DEPARTMENT_
NAME
1 John Smith CEO 1 Executive
2 Sarah Goodes CFO 1 Executive
3 Wayne Ablett CIO 1 Executive
4 Michelle Carey HR Manager 2 HR
5 Chris Matthews Sales Manager 3 Sales
6 Andrew Judd Development Manager 4 Development
7 Danielle McLeod Support Manager 5 Support
8 Matthew Swan HR Representative 2 HR
9 Stephanie Richardson Salesperson 2 HR
10 Tony Grant Salesperson 3 Sales
11 Jenna Lockett Front-End Developer 4 Development
12 Michael Dunstall Back-End Developer 4 Development
13 Jane Voss Back-End Developer 4 Development
14 Anthony Hird Support (null) (null)
15 Natalie Rocca Support 5 Support
(null) (null) (null) 6 Research

You can see it shows a full list of employees and departments. It shows the Anthony Hird record, even though he has no department. It also shows the Research department even though it has no employees.

We can also represent this data side by side:

EMPLOYEE _
ID
FULL_NAME DEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1 John Smith 1 1 Executive
2 Sarah Goodes 1 2 HR
3 Wayne Ablett 1 3 Sales
4 Michelle Carey 2 4 Development
5 Chris Matthews 3 5 Support
6 Andrew Judd 4 6 Research
7 Danielle McLeod 5
8 Matthew Swan 2
9 Stephanie Richardson 2
10 Tony Grant 3
11 Jenna Lockett 4
12 Michael Dunstall 4
13 Jane Voss 4
14 Anthony Hird (null)
15 Natalie Rocca 5

The grey rows are those that are not shown by the query.

 

Cross Join

You might have heard of the Cross Join. It’s also referred to as a Cartesian Join or a Cartesian Product.

A Cross Join is where you show records from both tables in all possible combinations. No matching is performed on columns. It shows every record from table1 matched to every record from table2.

This is how the data is loaded:

  1. For each record in table1
    1. For each record in table2
      1. Show data from table1 and table2

There is no filtering of the data here.

Here is an example of a Cross Join in SQL:

SELECT e.employee_id, e.full_name, e.job_role,

d.department_id, d.department_name

FROM employee e

CROSS JOIN department d;

Results:

EMPLOYEE_
ID
FULL_NAME JOB_ROLE DEPARTMENT_
ID
DEPARTMENT_
NAME
1 John Smith CEO 1 Executive
2 Sarah Goodes CFO 1 Executive
3 Wayne Ablett CIO 1 Executive
4 Michelle Carey HR Manager 1 Executive
5 Chris Matthews Sales Manager 1 Executive
6 Andrew Judd Development Manager 1 Executive
7 Danielle McLeod Support Manager 1 Executive
8 Matthew Swan HR Representative 1 Executive
9 Stephanie Richardson Salesperson 1 Executive
10 Tony Grant Salesperson 1 Executive
11 Jenna Lockett Front-End Developer 1 Executive
12 Michael Dunstall Back-End Developer 1 Executive
13 Jane Voss Back-End Developer 1 Executive
14 Anthony Hird Support 1 Executive
15 Natalie Rocca Support 1 Executive
1 John Smith CEO 2 HR
2 Sarah Goodes CFO 2 HR
3 Wayne Ablett CIO 2 HR
4 Michelle Carey HR Manager 2 HR
5 Chris Matthews Sales Manager 2 HR
6 Andrew Judd Development Manager 2 HR
7 Danielle McLeod Support Manager 2 HR
8 Matthew Swan HR Representative 2 HR
9 Stephanie Richardson Salesperson 2 HR

These results show all employees in all departments, and it has been trimmed so we don’t take up many more pages.

It’s an incorrect representation of the data, because not all employees belong to these departments.

These Cross Joins or cartesian products often happen if you don’t specify a join between two tables, usually by accident.

For example, this query selects from two tables, but there is no join.

SELECT e.employee_id, e.full_name, e.job_role,

d.department_id, d.department_name

FROM employee e, department d;

I’ve done this on many occasions, especially on larger queries. I accidentally leave out a join and all of a sudden I have a cartesian product.

Oracle allows you to specify a CROSS JOIN, as shown in the example earlier, so you can specify that you deliberately want this type of join.

However, it’s very rare that this type of join is actually used. This is because it results in an incorrect mapping of data and a lot more rows than is usually needed.

The only example of a cross join I’ve ever seen is to help generate data.

For example:

  1. You have a table of employees, and a table of dates that represent workdays for example.
  2. You want to generate a list that contains all dates and all employees.
  3. You can use a cross join to get an output of this data – all combinations of employees and dates.

Other than an example like this, there is probably no need to ever use a cross join.

 

Natural Join

A natural join in SQL is a variation of an inner join.

With a natural join, you don’t need to specify the columns. Oracle will work out which columns to join on based on the tables. It will join on two columns that have the same name.

This makes it simpler to write. You just specify the two tables and Oracle does the rest.

However, there are a couple of drawbacks:

  1. You can’t use a WHERE clause on a column in a natural join. If you do, you’ll get an error.
  2. If the columns change in a table, the natural join will break.
  3. You can’t use table aliases with a natural join (and I love table aliases)

Here’s an example of an SQL statement:

SELECT employee_id, full_name, job_role, department_id, department_name

FROM employee

NATURAL JOIN department;

Results:

EMPLOYEE_
ID
FULL_NAME JOB_ROLE DEPARTMENT_
ID
DEPARTMENT_
NAME
1 John Smith CEO 1 Executive
2 Sarah Goodes CFO 1 Executive
3 Wayne Ablett CIO 1 Executive
4 Michelle Carey HR Manager 2 HR
5 Chris Matthews Sales Manager 3 Sales
6 Andrew Judd Development Manager 4 Development
7 Danielle McLeod Support Manager 5 Support
8 Matthew Swan HR Representative 2 HR
9 Stephanie Richardson Salesperson 2 HR
10 Tony Grant Salesperson 3 Sales
11 Jenna Lockett Front-End Developer 4 Development
12 Michael Dunstall Back-End Developer 4 Development
13 Jane Voss Back-End Developer 4 Development
15 Natalie Rocca Support 5 Support

We can also represent this data side by side, the same way as an inner join:

EMPLOYEE _
ID
FULL_NAME DEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1 John Smith 1 1 Executive
2 Sarah Goodes 1 2 HR
3 Wayne Ablett 1 3 Sales
4 Michelle Carey 2 4 Development
5 Chris Matthews 3 5 Support
6 Andrew Judd 4 6 Research
7 Danielle McLeod 5
8 Matthew Swan 2
9 Stephanie Richardson 2
10 Tony Grant 3
11 Jenna Lockett 4
12 Michael Dunstall 4
13 Jane Voss 4
14 Anthony Hird (null)
15 Natalie Rocca 5

The grey rows are those that are not shown by the query.

 

Lateral, Cross Apply and Outer Apply

There are three new join types in Oracle 12c:

  • Lateral
  • Cross Apply
  • Outer Apply

These join types allow you to join subqueries inside the SELECT clause (an inline view) to the main query.

Here’s an example:

SELECT e.employee_id, e.full_name, sub.department_name

FROM employee e,

  (SELECT d.department_name

  FROM department d

  WHERE d.department_id = e.department_id)

AS sub;

This query will give you an error in versions of Oracle before 12c.

However, in Oracle 12c, you can add the LATERAL keyword to allow you to do this.

SELECT e.employee_id, e.full_name, sub.department_name

FROM employee e,

  LATERAL (SELECT d.department_name

  FROM department d

  WHERE d.department_id = e.department_id)

AS sub;

The LATERAL keyword allows you to specify the table to the left of the LATERAL keyword inside the inline view to the right of the keyword.

It allows for inline views to work like correlated subqueries.

A Cross Apply is similar to Lateral but it works like a cross join. It displays all records from both tables.

Finally, an Outer Apply is like a Lateral but uses a left outer join.

They are new features to Oracle 12c, so unless you are using Oracle 12c and need to perform this kind of logic, there isn’t really a need to use them.

 

Self Join

A self join is where you query a table and then join the table to itself.

A self join isn’t really an explicit join type. It’s a way of implementing one of the join types. It’s more of a joining concept than a join type.

This is because you would use a join type (e.g. INNER, LEFT JOIN) with a self join.

Why would you use a self join?

One common example is writing hierarchical queries.

  • We have employees and managers
  • Managers are also employees
  • The employee table has a manager_id column, which refers to an employee record
  • You might want to know what the manager details are of an employee

Here’s our employee data example:

EMPLOYEE_
ID
FULL_NAME DEPARTMENT_
ID
JOB_ROLE MANAGER_
ID
1 John Smith 1 CEO (null)
2 Sarah Goodes 1 CFO 1
3 Wayne Ablett 1 CIO 1
4 Michelle Carey 2 HR Manager 1
5 Chris Matthews 3 Sales Manager 2
6 Andrew Judd 4 Development Manager 3
7 Danielle McLeod 5 Support Manager 3
8 Matthew Swan 2 HR Representative 4
9 Stephanie Richardson 2 Salesperson 5
10 Tony Grant 3 Salesperson 5
11 Jenna Lockett 4 Front-End Developer 6
12 Michael Dunstall 4 Back-End Developer 6
13 Jane Voss 4 Back-End Developer 6
14 Anthony Hird (null) Support 7
15 Natalie Rocca 5 Support 7

Now, here’s how we would write a self join in SQL:

SELECT e.employee_id, e.full_name, e.job_role,

m.employee_id AS manager_id, m.full_name AS manager_full_name

FROM employee e

INNER JOIN employee m ON e.manager_id = m.employee_id;

Results:

EMPLOYEE_
ID
FULL_NAME JOB_ROLE DEPARTMENT_
ID
MANAGER_
FULL_NAME
4 Michelle Carey HR Manager 1 John Smith
3 Wayne Ablett CIO 1 John Smith
2 Sarah Goodes CFO 1 John Smith
5 Chris Matthews Sales Manager 2 Sarah Goodes
7 Danielle McLeod Support Manager 3 Wayne Ablett
6 Andrew Judd Development Manager 3 Wayne Ablett
8 Matthew Swan HR Representative 4 Michelle Carey
10 Tony Grant Salesperson 5 Chris Matthews
9 Stephanie Richardson Salesperson 5 Chris Matthews
13 Jane Voss Back-End Developer 6 Andrew Judd
12 Michael Dunstall Back-End Developer 6 Andrew Judd
11 Jenna Lockett Front-End Developer 6 Andrew Judd
15 Natalie Rocca Support 7 Danielle McLeod
14 Anthony Hird Support 7 Danielle McLeod

This query selects from the employee table twice. I’ve given one table the alias of e (for employee) and the other an alias of m (for manager).

In the SELECT clause, I’ve specified a few columns from the employee table, and then two columns from the employee table marked as manager. I’ve given those column aliases to be clear that they belong to the manager (manager_id and manager_full_name).

Now, the join is where the magic happens.

I’ve joined the employee table (e) to the manager employee table (m) where the employee’s manager ID equals the manager’s employee ID.

This is where the table joins to itself.

It results in a set of data that shows managers and employees.

If the join is the other way around, then it won’t work.

Also, table aliases are essential here. Without labelling the tables as e or m (or any other aliases), this example would not have worked.

 

Equijoin and Non-Equijoin

An equijoin and a non-equijoin are not join types either. They are ways of describing one of the join types.

An equijoin is a join where the two columns are matched using the equals sign:

SELECT columns

FROM table1

INNER JOIN table2 ON table1.start_date = table2_effective_date;

It means that the values in the two columns must be equal. The column names can be different though.

A non-equijoin is where the two columns are matched using any sign other than the equals sign.

This could be >=, <=, >, < or !=.

For example:

SELECT columns

FROM table1

INNER JOIN table2 ON table1.start_date <= table2_effective_date;

An equijoin and non-equijoin can be used as part of the join types I’ve mentioned in this guide.

 

Comparison of Join Types

So, we’ve covered all of the different join types in Oracle.

This table summarises the differences between them.

Criteria Inner Left Right Full Cross Natural
All records in table 1? No Yes No Yes Yes No
All records in table 2? No No Yes Yes Yes No
Allow different column names? Yes Yes Yes Yes N/A No

 

Inner Join vs Outer Join

So we’ve look at both inner joins and outer joins.

We’ve seen some examples.

But what’s the difference?

Inner joins will show you the data that exists in both tables that are being joined. Outer joins will show you the data in one table, and if any results are found in the joining table, it shows those, and shows NULL if no matches are found.

In short, inner joins show complete matches, and outer joins show matches and NULLs for non-matches.

At the end of this article I have a diagram that helps you decide which join to use depending on what data you want to see.

 

How to Join 3 Tables in SQL

We’ve seen some examples for joining tables in SQL. The examples all show how to join two tables together: the employee and department tables.

You can use the same syntaxto join two tables of your own.

But what if you want to join more than two tables? Can you use SQL joins to join three tables?

Yes, you can.

You can link the tables together like pipes, where you join the compatible parts of them together.

The syntax looks like this:

SELECT columns

FROM table1

JOIN_TYPE table2 ON table1.column1 = table2.column1

JOIN_TYPE table3 ON table2.column2 = table3.column1

To add the third table to your join, you can add another line to your SQL query that includes the third table and the columns you want to join on.

When you join the third table, it doesn’t matter which table you specify you’re joining from. In this table, you could join from table1 or table2. It depends on which columns you are joining on.

The only rule is that the table you’re joining from needs to have already been mentioned in your query. In this example both tables have been.

For example, let’s say we had a table called location, where departments were located (the sales department could be in Chicago, the HR department could be in Boston).

Our query could look something like this:

SELECT first_name, last_name, department_name, office_location

FROM employee

INNER JOIN department ON employee.department_id = department.department_id

INNER JOIN location ON department.location_id = location.location_id;

 

Can you use different join types for each table?

Yes, you can use different join types for each table you’re joining to. You can INNER JOIN to one table, and LEFT JOIN to another. You can use any combination of joins.

This also applies no matter how many tables you’re joining to.

For example, let’s say we want to see all employees, their departments, and if the department has a location, show that.

SELECT first_name, last_name, department_name, office_location

FROM employee

INNER JOIN department ON employee.department_id = department.department_id

LEFT JOIN location ON department.location_id = location.location_id;

We’ve used a LEFT JOIN to location so that we can see all of the employee and department information, even if no location was set.

 

What’s Specific about Joins in Oracle?

In Oracle, the join keywords work the same, as they are ANSI standard.

The only difference with joins in Oracle is the non-ANSI standard version that they have also implemented.

Let’s look at that now.

 

Alternative Join Syntax (Non-ANSI)

There is an alternative way that you can write joins. It’s an older way of writing them.

It uses the WHERE clause.

If you think about the way that joins work, it matches two tables together where a column in one table matches a column in another table.

This can also be done using a WHERE clause.

Inner Joins

For example, this is an inner join using the ANSI standard syntax:

SELECT e.full_name, e.job_role, d.department_name

FROM employee e

JOIN department d ON e.department_id = d.department_id;

And this is the same join using the WHERE clause:

SELECT e.full_name, e.job_role, d.department_name

FROM employee e, department d

WHERE e.department_id = d.department_id;

This performs a match on the two columns and gives the same result.

Outer Joins

How would you do outer joins?

Oracle has implemented an enhancement to this syntax, which allows you to use outer joins with the WHERE clause.

It uses a plus sign in brackets: (+).

You put this after the column in the WHERE clause on one side. It goes on the side that you want to show NULL records for.

So, this is a left join:

SELECT e.full_name, e.job_role, d.department_name

FROM employee e, department d

WHERE e.department_id = d.department_id (+);

And this is a right join:

SELECT e.full_name, e.job_role, d.department_name

FROM employee e, department d

WHERE e.department_id (+) = d.department_id;

And this is a full join:

SELECT e.full_name, e.job_role, d.department_name

FROM employee e, department d

WHERE e.department_id (+) = d.department_id (+);

It’s Better To Use the ANSI Standard, Not This Syntax

Why is it better to use the ANSI standard (using the JOIN keywords) and not this standard (using the WHERE clause)?

There are a few reasons.

Firstly, it’s easier to see that you have joined everything.

In large queries, it’s common to have many tables. Using the ANSI standard and the JOIN keywords, you need to specify each table and then a join, such as:

“… FROM table1 JOIN table2 ON col1=col1 JOIN table3 ON col2=col2…”

Notice that I needed to specify another JOIN word to include table3 into the query.

In the non-ANSI format, you specify all of your tables at once, and then use the WHERE clause to join them. It’s easy to miss a table – which I have done many times, and ended up with a cartesian product or an incorrect result

An example would be:

“… FROM table1, table2, table3

WHERE table1.col = table2.col AND table2.col = table3.col…”

For this reason, it’s better to use the ANSI format.

The second reason is that it’s easier to see what type of join you’re using.

You can easily identify if you’re using an inner join, left/right join, full join, or something else.

With the non-ANSI standard, you have the plus signs, which can get confusing, especially if you put them on the wrong side.

So, knowing the non-ANSI format is good if you’re looking at existing code, but for any future development, I recommend using the ANSI format.

 

Best Practices for Using Joins

Now you’ve learnt about all the join types, what are the best practices for using them? What recommendations can I offer you for using joins in your queries?

Avoid the USING Keyword

The USING keyword is a keyword that you can use as part of the JOIN syntax.

It allows you to specify the column name once, instead of twice, when writing a query.

A query would look like this:

SELECT employee_id, full_name, job_role, department_id, department_name

FROM employee

JOIN department USING (department_id);

EMPLOYEE_
ID
FULL_NAME JOB_ROLE DEPARTMENT
_ID
DEPARTMENT_
NAME
1 John Smith CEO 1 Executive
2 Sarah Goodes CFO 1 Executive
3 Wayne Ablett CIO 1 Executive
4 Michelle Carey HR Manager 2 HR
5 Chris Matthews Sales Manager 3 Sales
6 Andrew Judd Development Manager 4 Development
7 Danielle McLeod Support Manager 5 Support
8 Matthew Swan HR Representative 2 HR
9 Stephanie Richardson Salesperson 2 HR
10 Tony Grant Salesperson 3 Sales
11 Jenna Lockett Front-End Developer 4 Development
12 Michael Dunstall Back-End Developer 4 Development
13 Jane Voss Back-End Developer 4 Development
15 Natalie Rocca Support 5 Support

This keyword uses the INNER JOIN logic and joins both tables based on the department_id column.

To use the USING keyword, the column name in both tables must be the same. In this case, they are both called department_id.

There are a few problems with this, which is why I recommend avoiding the USING keyword.

  • What if the column names change? Your query will no longer run.
  • What if the column names are different? You can’t use this keyword to begin with.
  • You also can’t use this collumn in a WHERE clause.
  • You can’t use table aliases with this kind of query either – you’ll get an error.

For these reasons, it’s better to use the regular JOIN ON syntax.

Use ANSI Standard

I mentioned earlier in this guide that there is a non-ANSI form of join syntax, that uses the WHERE clause and (+) notation to allow for outer joins.

As I suggested, I would avoid using this syntax because it’s not standard, it’s harder to read, and easy to forget a table.

Use Table Aliases

When selecting from multiple tables, or using many tables in your query, the query can get pretty long pretty quickly!

It can also get harder to know which table you’re referring to in your query, especially if they have similar names or long names.

So, I recommend using table aliases in all of your tables.

It makes it easier to write JOIN statements, and queries in general.

It helps readability, and allows you to write queries faster.

It also helps with your IDE’s autocomplete feature. You can just type the alias name then a period to get the autocomplete to appear, instead of the full table name.

You might have noticed in all of my sample queries I have used table aliases (employee e and department d). I really like the benefits they bring.

 

Which Join Type Should I Use?

So, after all of that, how do you know which join type to use?

I’ve prepared a flowchart here for you.

Which SQL Join Should I Use

Simply think about your current situation or query, answer the questions in the flowchart, and you’ll get to a result!

 

More Information

This covers all you need to know about joins in Oracle SQL.

You can use the SQL I’ve provided in this guide to write your own queries and test out the joins yourself.

Hope you’ve enjoyed the guide!

Download Free Guide: "6 Examples of Joins"

Download Your FREE Guide: "6 Examples of Joins"