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_IDDEPARTMENT_NAME
1Executive
2HR
3Sales
4Development
5Support
6Research

And this is the employee table.

EMPLOYEE_
ID
FULL_NAMEDEPARTMENT_
ID
ROLEMANAGER_
ID
1John Smith1CEO(null)
2Sarah Goodes1CFO1
3Wayne Ablett1CIO1
4Michelle Carey2HR Manager1
5Chris Matthews3Sales Manager2
6Andrew Judd4Development Manager3
7Danielle McLeod5Support Manager3
8Matthew Swan2HR Representative4
9Stephanie Richardson2Salesperson5
10Tony Grant3Salesperson5
11Jenna Lockett4Front-End Developer6
12Michael Dunstall4Back-End Developer6
13Jane Voss4Back-End Developer6
14Anthony Hird(null)Support7
15Natalie Rocca5Support7

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_NAMEROLEDEPARTMENT_NAME
John SmithCEOExecutive
Sarah GoodesCFOExecutive
Wayne AblettCIOExecutive
Michelle CareyHR ManagerHR
Chris MatthewsSales ManagerSales
Andrew JuddDevelopment ManagerDevelopment
Danielle McLeodSupport ManagerSupport
Matthew SwanHR RepresentativeHR
Stephanie RichardsonSalespersonHR
Tony GrantSalespersonSales
Jenna LockettFront-End DeveloperDevelopment
Michael DunstallBack-End DeveloperDevelopment
Jane VossBack-End DeveloperDevelopment
Natalie RoccaSupportSupport

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_NAMEDEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1John Smith11Executive
2Sarah Goodes12HR
3Wayne Ablett13Sales
4Michelle Carey24Development
5Chris Matthews35Support
6Andrew Judd46Research
7Danielle McLeod5
8Matthew Swan2
9Stephanie Richardson2
10Tony Grant3
11Jenna Lockett4
12Michael Dunstall4
13Jane Voss4
14Anthony Hird(null)
15Natalie Rocca5

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_NAMEJOB_ROLEDEPARTMENT_
ID
DEPARTMENT_
NAME
3Wayne AblettCIO1Executive
2Sarah GoodesCFO1Executive
1John SmithCEO1Executive
9Stephanie RichardsonSalesperson2HR
8Matthew SwanHR Representative2HR
4Michelle CareyHR Manager2HR
10Tony GrantSalesperson3Sales
5Chris MatthewsSales Manager3Sales
13Jane VossBack-End Developer4Development
12Michael DunstallBack-End Developer4Development
11Jenna LockettFront-End Developer4Development
6Andrew JuddDevelopment Manager4Development
15Natalie RoccaSupport5Support
7Danielle McLeodSupport Manager5Support
14Anthony HirdSupport(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_NAMEDEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1John Smith11Executive
2Sarah Goodes12HR
3Wayne Ablett13Sales
4Michelle Carey24Development
5Chris Matthews35Support
6Andrew Judd46Research
7Danielle McLeod5
8Matthew Swan2
9Stephanie Richardson2
10Tony Grant3
11Jenna Lockett4
12Michael Dunstall4
13Jane Voss4
14Anthony Hird(null)
15Natalie Rocca5

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_NAMEJOB_ROLEDEPARTMENT_
ID
DEPARTMENT_
NAME
1John SmithCEO1Executive
2Sarah GoodesCFO1Executive
3Wayne AblettCIO1Executive
4Michelle CareyHR Manager2HR
5Chris MatthewsSales Manager3Sales
6Andrew JuddDevelopment Manager4Development
7Danielle McLeodSupport Manager5Support
8Matthew SwanHR Representative2HR
9Stephanie RichardsonSalesperson2HR
10Tony GrantSalesperson3Sales
11Jenna LockettFront-End Developer4Development
12Michael DunstallBack-End Developer4Development
13Jane VossBack-End Developer4Development
15Natalie RoccaSupport5Support
(null)(null)(null)6Research

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_NAMEDEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1John Smith11Executive
2Sarah Goodes12HR
3Wayne Ablett13Sales
4Michelle Carey24Development
5Chris Matthews35Support
6Andrew Judd46Research
7Danielle McLeod5
8Matthew Swan2
9Stephanie Richardson2
10Tony Grant3
11Jenna Lockett4
12Michael Dunstall4
13Jane Voss4
14Anthony Hird(null)
15Natalie Rocca5

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_NAMEJOB_ROLEDEPARTMENT_
ID
DEPARTMENT_
NAME
1John SmithCEO1Executive
2Sarah GoodesCFO1Executive
3Wayne AblettCIO1Executive
4Michelle CareyHR Manager2HR
5Chris MatthewsSales Manager3Sales
6Andrew JuddDevelopment Manager4Development
7Danielle McLeodSupport Manager5Support
8Matthew SwanHR Representative2HR
9Stephanie RichardsonSalesperson2HR
10Tony GrantSalesperson3Sales
11Jenna LockettFront-End Developer4Development
12Michael DunstallBack-End Developer4Development
13Jane VossBack-End Developer4Development
14Anthony HirdSupport(null)(null)
15Natalie RoccaSupport5Support
(null)(null)(null)6Research

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_NAMEDEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1John Smith11Executive
2Sarah Goodes12HR
3Wayne Ablett13Sales
4Michelle Carey24Development
5Chris Matthews35Support
6Andrew Judd46Research
7Danielle McLeod5
8Matthew Swan2
9Stephanie Richardson2
10Tony Grant3
11Jenna Lockett4
12Michael Dunstall4
13Jane Voss4
14Anthony Hird(null)
15Natalie Rocca5

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_NAMEJOB_ROLEDEPARTMENT_
ID
DEPARTMENT_
NAME
1John SmithCEO1Executive
2Sarah GoodesCFO1Executive
3Wayne AblettCIO1Executive
4Michelle CareyHR Manager1Executive
5Chris MatthewsSales Manager1Executive
6Andrew JuddDevelopment Manager1Executive
7Danielle McLeodSupport Manager1Executive
8Matthew SwanHR Representative1Executive
9Stephanie RichardsonSalesperson1Executive
10Tony GrantSalesperson1Executive
11Jenna LockettFront-End Developer1Executive
12Michael DunstallBack-End Developer1Executive
13Jane VossBack-End Developer1Executive
14Anthony HirdSupport1Executive
15Natalie RoccaSupport1Executive
1John SmithCEO2HR
2Sarah GoodesCFO2HR
3Wayne AblettCIO2HR
4Michelle CareyHR Manager2HR
5Chris MatthewsSales Manager2HR
6Andrew JuddDevelopment Manager2HR
7Danielle McLeodSupport Manager2HR
8Matthew SwanHR Representative2HR
9Stephanie RichardsonSalesperson2HR

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_NAMEJOB_ROLEDEPARTMENT_
ID
DEPARTMENT_
NAME
1John SmithCEO1Executive
2Sarah GoodesCFO1Executive
3Wayne AblettCIO1Executive
4Michelle CareyHR Manager2HR
5Chris MatthewsSales Manager3Sales
6Andrew JuddDevelopment Manager4Development
7Danielle McLeodSupport Manager5Support
8Matthew SwanHR Representative2HR
9Stephanie RichardsonSalesperson2HR
10Tony GrantSalesperson3Sales
11Jenna LockettFront-End Developer4Development
12Michael DunstallBack-End Developer4Development
13Jane VossBack-End Developer4Development
15Natalie RoccaSupport5Support

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

EMPLOYEE _
ID
FULL_NAMEDEPARTMENT _
ID
DEPARTMENT
_ID
DEPARTMENT
_NAME
1John Smith11Executive
2Sarah Goodes12HR
3Wayne Ablett13Sales
4Michelle Carey24Development
5Chris Matthews35Support
6Andrew Judd46Research
7Danielle McLeod5
8Matthew Swan2
9Stephanie Richardson2
10Tony Grant3
11Jenna Lockett4
12Michael Dunstall4
13Jane Voss4
14Anthony Hird(null)
15Natalie Rocca5

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_NAMEDEPARTMENT_
ID
JOB_ROLEMANAGER_
ID
1John Smith1CEO(null)
2Sarah Goodes1CFO1
3Wayne Ablett1CIO1
4Michelle Carey2HR Manager1
5Chris Matthews3Sales Manager2
6Andrew Judd4Development Manager3
7Danielle McLeod5Support Manager3
8Matthew Swan2HR Representative4
9Stephanie Richardson2Salesperson5
10Tony Grant3Salesperson5
11Jenna Lockett4Front-End Developer6
12Michael Dunstall4Back-End Developer6
13Jane Voss4Back-End Developer6
14Anthony Hird(null)Support7
15Natalie Rocca5Support7

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_NAMEJOB_ROLEDEPARTMENT_
ID
MANAGER_
FULL_NAME
4Michelle CareyHR Manager1John Smith
3Wayne AblettCIO1John Smith
2Sarah GoodesCFO1John Smith
5Chris MatthewsSales Manager2Sarah Goodes
7Danielle McLeodSupport Manager3Wayne Ablett
6Andrew JuddDevelopment Manager3Wayne Ablett
8Matthew SwanHR Representative4Michelle Carey
10Tony GrantSalesperson5Chris Matthews
9Stephanie RichardsonSalesperson5Chris Matthews
13Jane VossBack-End Developer6Andrew Judd
12Michael DunstallBack-End Developer6Andrew Judd
11Jenna LockettFront-End Developer6Andrew Judd
15Natalie RoccaSupport7Danielle McLeod
14Anthony HirdSupport7Danielle 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.

CriteriaInnerLeftRightFullCrossNatural
All records in table 1?NoYesNoYesYesNo
All records in table 2?NoNoYesYesYesNo
Allow different column names?YesYesYesYesN/ANo

 

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_NAMEJOB_ROLEDEPARTMENT
_ID
DEPARTMENT_
NAME
1John SmithCEO1Executive
2Sarah GoodesCFO1Executive
3Wayne AblettCIO1Executive
4Michelle CareyHR Manager2HR
5Chris MatthewsSales Manager3Sales
6Andrew JuddDevelopment Manager4Development
7Danielle McLeodSupport Manager5Support
8Matthew SwanHR Representative2HR
9Stephanie RichardsonSalesperson2HR
10Tony GrantSalesperson3Sales
11Jenna LockettFront-End Developer4Development
12Michael DunstallBack-End Developer4Development
13Jane VossBack-End Developer4Development
15Natalie RoccaSupport5Support

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"