Introduction



Nowadays, the majority of organizations use Relational Database Management Systems (RDBMS) to organize and store their data. Oracle, MS SQL Server, Postgree or MySQL – it doesn’t really matter when it comes to the main principles and rules used to express what we want and how we want it from a relational point of view. The Structured Query Language (SQL) is the common standard domain specific language used to establish this communication, and the SQL joins are the primary means to combine the data.

Most of us know the basic types of joins – Inner and Outer joins – since they’re the usual suspects in our day-to-day tasks. But there are a few more joins that are worth mentioning, at least from a theoretical point of view. Do you know what's a Theta join ? The difference between Inner, Equi and Non-Equi join, what is a Cartesian product, what is a Natural join, what are the different types of Outer joins, what’s the connection between all of them ?

Keep reading to find out.

The Bigger Picture



One picture is worth a thousand words. So let’s not waste time.

In this diagram I’ve made you can see the whole hierarchy of joins, starting with the most generic

Cross join and finishing with the Natural join. The types of joins we usually use in our day to day work are the Equi joins. The Theta joins are more of a theoretical importance and are seldom used in practice. Let’s go through all of them.



The example schema



For my examples, I’ll use the following two simple tables with a few records inside for easy understanding.

Employees table:



Departments table:

Also note that the examples I provide are generic and can vary from implementation to implementation (I've used Oracle and SQL Developer). The gray rectangles are abstract types of joins (see the difference between Inner and Equi join below).

Cross Join (Cartesian product)



The Cross join is the most generic type of join, it generally means connect anything with anything. Its result is the number of records in the first table multiplied by the number of records in the second table, showing the columns altogether.

You can perform Cross join using one of the following queries:

SELECT * FROM Departments, Employees

or

SELECT * FROM Departments CROSS JOIN Employees

Which will result in



The use of the Cartesian join is strongly discouraged. If you feel you need it, consider rethinking your design.

Inner Join



​

The Inner Join refers to the intersection of two (or more) tables. For example, to get all the employees who have departments and all the departments who have employees, we’ll use the one of the following queries



SELECT * FROM Departments d, Employees e WHERE d.ID = e.DepartmentID

or

SELECT * FROM Departments d INNER JOIN Employees e ON d.ID = e.DepartmentID







If you want to get results only from the Development department, you’ll use something like this

SELECT * FROM Departments d, Employees e WHERE d.ID = e.DepartmentID AND d.ID = 1

Outer Joins



The Outer Joins consist of the intersection and the complements of the tables. There are three general types of outer joins:

• Full Outer Join

• Left Outer Join

• Right Outer Join

Full Outer Join



​The Full Outer join consists of the intersection of the tables along with the two relative complements. Simply said, this type of join will act like an inner join but will also return all records that have NULL for their foreign key relations:

SELECT * FROM Departments d FULL OUTER JOIN Employees e ON d.ID = e.DepartmentID

will produce

As you can see, the Employees that don’t have any departments associated along with the departments which don’t have any employees associated are also returned.

Left Outer Join



​The Left Outer join will return the records that intersect, along with the records from the left table which don’t have any assigned records from the right table. In our case:

SELECT * FROM Departments d LEFT OUTER JOIN Employees e ON d.ID = e.DepartmentID;

resulting in

Right Outer Join



​The Right Outer join is essentially the same like the Left Outer Join, but with the right table’s complement.

SELECT * FROM Departments d RIGHT OUTER JOIN Employees e ON d.ID = e.DepartmentID;





As you can see, the employees without a department are also returned.

Equi Join



In fact, the examples I just gave were not just Inner Joins and Outer Joins. They ware also Equi Joins. And that’s the exact type of join you use when you write a SELECT statement with the equals binary operator. When someone talk about an Inner join, they usually mean an Inner Equi Join.

Theta Join



The Theta join (also known as the Non-equi join) is the kind of join you'll produce if you replace or combine the equals operator with something else. For example:

SELECT * FROM Departments d, Employees e WHERE d.ID < e.DepartmentID

or

SELECT * FROM Departments d, Employees e WHERE d.ID <= e.DepartmentID

Natural Join



The Natural join is more or less a convenience. It compares the columns of the tables and performs an Equi Join on them, which can either be Inner or Outer. In order to use this join, you’ll have to follow a specific naming convention. For example, in my case I should change the ID column of Departments to Department_ID and the foreign key column of Employees to Department_ID.

Having done that, the Natural join will be able to match these two and perform. For example:

SELECT * FROM Departments NATURAL JOIN Employees

This will perform an Inner Join by default. If we want to make it an Outer Natural Join, we can do it like this:

SELECT * FROM Departments NATURAL LEFT OUTER JOIN Employees

A Few Tips on Using SQL Joins



By INNER JOIN, we usually mean INNER EQUI JOIN

By LEFT JOIN, we usually mean LEFT OUTER EQUI JOIN (RIGHT JOIN respectively)

The INTERSECT operation is not the same as the INNER JOIN

The UNION operation is not the same as the FULL OUTER JOIN

Don’t use the Cross Join. If you think you need it, better reconsider your design.

Although you can use the Natural join, I would advise you against that for a few reasons

​​1. Stating the column names explicitly makes your intent clearer.

2. In order to utilize it, you’ll need to use a specific naming convention that may conflict with the one you currently follow.

3. If you use an Object Relation Mapper, you should think twice before choosing a proper table name (I would not like to access my department id like dept.Department_ID. I would prefer dept.ID). ​

For more information on the topic, check Kyle Hailey's article on SQL relations.

Kosta Hristov ( 34 Posts Hi there ! My name is Kosta Hristov and I currently live in London, England. I've been working as a software engineer for the past 6 years on different mobile, desktop and web IT projects. I started this blog almost one year ago with the idea of helping developers from all around the world in their day to day programming tasks, sharing knowledge on various topics. If you find my articles interesting and you want to know more about me, feel free to contact me via the social links below. ;)

Like the article ? Share it ! ;)