Home > Database > Types of Joins in SQL

Types of Joins in SQL


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


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


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

full-outer-join-setThe 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

sql-left-outer-join-setThe 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

sql-right-outer-join-setThe 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


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:



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.



Like the article ? Share it ! ;)

  1. June 26th, 2013 at 08:11 | #1

    Nice writeup. Also reminded me to post something on how the joins are more complicated than the nice circle set diagrams. Here is a quick write up http://dboptimizer.com/2013/06/26/3107/


    – Kyle Hailey

  2. June 27th, 2013 at 15:40 | #2

    Hi Kyle, 

    Glad you like it. Your article at dboptimizer would be a nice reference for the readers, hope you won't mind if I add a link at the end. 


  3. July 30th, 2013 at 18:00 | #3

    Good article.  I've passed the URL to my OCA students.

    Hemant K Chitale

  4. August 4th, 2013 at 21:35 | #4

    Hi Hemant, 

    Sure, no problem at all. 


  5. February 11th, 2014 at 15:29 | #5

    Nice writeup I think that you missed one practical example of a theta join, however. For want of a better name, I call it a Range Join. It is commonly used in situations such as a tax table or a shipping-weight table.

    SELECT PackageID, PackageWeight, WeightRanges.Cost FROM Packages, WeightRanges WHERE PackageWeight BETWEEN WeightRanges.LowBound AND WeightRanges.HighBound


  6. March 19th, 2014 at 12:22 | #6

    nice work

  7. pai1009
    September 26th, 2014 at 12:49 | #7

    Nice article. Similar approach for learning sql joins you can find on Vertabelo blog: http://www.vertabelo.com/blog/technical-articles/sql-joins
    or Codding Horror: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/.

    There, joins are also visualized using Venn diagrams with example query and result (combined rows are highlighted, so it becomes more readable and better to understand).

    Of course, your article presents basic concept of sql joins in a perfect way, but for those who are interested in further reading I reccomend also those two resources.

  1. August 22nd, 2013 at 17:20 | #1
  2. February 23rd, 2014 at 16:15 | #2

Copyright © Developing the future 2013. Licensed under the CC> BY-NC-ND 3.0 Creative Commons license.       
Audi R8 wallpapers Sony Xperia Z4 Tablet WiFi