SQL Joins: The Complete Guide

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.

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:

And this is the employee table.

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. You can download this from my GitHub repository here.

 1CREATE TABLE department (
 2department_id NUMBER(5) PRIMARY KEY,
 3department_name VARCHAR2(100)
 4);
 5 
 6CREATE TABLE employee (
 7employee_id NUMBER(5) PRIMARY KEY,
 8full_name VARCHAR2(100),
 9department_id NUMBER(5) REFERENCES department(department_id),
10job_role VARCHAR2(100),
11manager_id NUMBER(5)
12);
13
14 
15
16INSERT INTO department(department_id, department_name) VALUES (1, 'Executive');
17INSERT INTO department(department_id, department_name) VALUES (2, 'HR');
18INSERT INTO department(department_id, department_name) VALUES (3, 'Sales');
19INSERT INTO department(department_id, department_name) VALUES (4, 'Development');
20INSERT INTO department(department_id, department_name) VALUES (5, 'Support');
21INSERT INTO department(department_id, department_name) VALUES (6, 'Research');
22
23INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
24VALUES (1, 'John Smith', 1, 'CEO', NULL);
25INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
26VALUES (2, 'Sarah Goodes', 1, 'CFO', 1);
27INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
28VALUES (3, 'Wayne Ablett', 1, 'CIO', 1);
29INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
30VALUES (4, 'Michelle Carey', 2, 'HR Manager', 1);
31INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
32VALUES (5, 'Chris Matthews', 3, 'Sales Manager ', 2);
33INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
34VALUES (6, 'Andrew Judd', 4, 'Development Manager', 3);
35INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
36VALUES (7, 'Danielle McLeod', 5, 'Support Manager', 3);
37INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
38VALUES (8, 'Matthew Swan', 2, 'HR Representative', 4);
39INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
40VALUES (9, 'Stephanie Richardson', 2, 'Salesperson', 5);
41INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
42VALUES (10, 'Tony Grant', 3, 'Salesperson', 5);
43INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
44VALUES (11, 'Jenna Lockett', 4, 'Front-End Developer', 6);
45INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
46VALUES (12, 'Michael Dunstall', 4, 'Back-End Developer', 6);
47INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
48VALUES (13, 'Jane Voss', 4, 'Back-End Developer', 6);
49INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
50VALUES (14, 'Anthony Hird', null, 'Support', 7);
51INSERT INTO employee (employee_id, full_name, department_id, job_role, manager_id)
52VALUES (15, 'Natalie Rocca', 5, 'Support', 7);

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.

If you want to watch my YouTube video on joins, you can watch it here:

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 primary key/foreign key field relationship.

The syntax for a join is:

1SELECT columns
2FROM table1
3JOIN_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.

Get a summary of the different types of joins on my SQL Cheat Sheet.

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.

The graphics for each type of join are based on two tables, like this:

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 diagram shows you how the join is represented.

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:

1SELECT
2e.full_name,
3e.job_role,
4d.department_name
5FROM employee e
6INNER JOIN department d ON e.department_id = d.department_id;

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

1SELECT
2e.full_name,
3e. job_role,
4d.department_name
5FROM employee e
6JOIN 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.

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:

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.

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.

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM employee e
8LEFT OUTER JOIN department d ON e.department_id = d.department_id;

These are the results for this query:

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

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM employee e
8LEFT JOIN department d ON e.department_id = d.department_id;

We can also represent this data side by side:

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.

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:

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM employee e
8RIGHT OUTER JOIN department d ON e.department_id = d.department_id;

Results:

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:

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM employee e
8RIGHT 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

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM employee e
8LEFT OUTER JOIN department d ON e.department_id = d.department_id;

Right Join

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM department d
8RIGHT OUTER JOIN employee e ON e.department_id = d.department_id;

We can also represent this data side by side:

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:

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:

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM employee e
8FULL OUTER JOIN department d ON e.department_id = d.department_id;

Results:

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:

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:

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM employee e
8CROSS JOIN department d;

Results:

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.

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5d.department_id,
6d.department_name
7FROM 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:

1SELECT
2employee_id,
3full_name,
4job_role,
5department_id,
6department_name
7FROM employee
8NATURAL JOIN department;

Results:

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

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:

1SELECT
2e.employee_id,
3e.full_name,
4sub.department_name
5FROM employee e,
6  (SELECT d.department_name
7  FROM department d
8  WHERE d.department_id = e.department_id)
9AS 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.

1SELECT
2e.employee_id,
3e.full_name,
4sub.department_name
5FROM employee e,
6  LATERAL (SELECT d.department_name
7  FROM department d
8  WHERE d.department_id = e.department_id)
9AS 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.

Read the guide on Cross Apply and Outer Apply here.

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:

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

1SELECT
2e.employee_id,
3e.full_name,
4e.job_role,
5m.employee_id AS manager_id,
6m.full_name AS manager_full_name
7FROM employee e
8INNER JOIN employee m ON e.manager_id = m.employee_id;

Results:

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.

You can watch my YouTube video on self-joins here:

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:

1SELECT columns
2FROM table1
3INNER 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:

1SELECT columns
2FROM table1
3INNER 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.

Inner Join vs Outer Join

So we've looked 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 of joining tables in SQL. The examples all show how to join two tables together: the employee and department tables.

You can use the same syntax to 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:

1SELECT columns
2FROM table1
3JOIN_TYPE table2 ON table1.column1 = table2.column1
4JOIN_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:

1SELECT
2first_name,
3last_name,
4department_name,
5office_location
6FROM employee
7INNER JOIN department ON employee.department_id = department.department_id
8INNER 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. 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.

1SELECT
2first_name,
3last_name,
4department_name,
5office_location
6FROM employee
7INNER JOIN department ON employee.department_id = department.department_id
8LEFT 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.

You can watch my video on joining three tables here:

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:

1SELECT
2e.full_name,
3e.job_role,
4d.department_name
5FROM employee e
6JOIN department d ON e.department_id = d.department_id;

And this is the same join using the WHERE clause:

1SELECT
2e.full_name,
3e.job_role,
4d.department_name
5FROM employee e, department d
6WHERE 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:

1SELECT
2e.full_name,
3e.job_role,
4d.department_name
5FROM employee e, department d
6WHERE e.department_id = d.department_id (+);

And this is a right join:

1SELECT
2e.full_name,
3e.job_role,
4d.department_name
5FROM employee e, department d
6WHERE e.department_id (+) = d.department_id;

And this is a full join:

1SELECT
2e.full_name,
3e.job_role,
4d.department_name
5FROM employee e, department d
6WHERE 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:

1“… 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:

1“… FROM table1, table2, table3
2WHERE 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:

1SELECT
2employee_id,
3full_name,
4job_role,
5department_id,
6department_name
7FROM employee
8JOIN department USING (department_id);

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 column 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.

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!