Types of joins

To my shame I still do not know all types of joins. This article is my try to figure out in this topic.

First, we should understand what join is.

Join is an operation that union tables by some columns.

There are several types of them:

1. left inner
2. right inner
3. left outer
4. right outer
5. some other types.

The main difference between all of them is what rows the result includes. Later, we will look at some examples in order to comprehend it better.

We will use 2 tables workers and phones in our examples.

select * from workers;

output:

---------------
name  | type
---------------
john  | developer
steve | manager
bruce | seller
neal  | architect

select * from phones;

output:

------------------
name   | phone
------------------
john   | 100
jeramy | 103
steve  | 102
zach   | 101

To better understand, we will execute sql queries with different type of joins.

left inner join

A query is given:

select * from workers left inner join phones on workers.name = phones.name;

The result of execution of the query includes workers who has phone numbers.

-------------------------------
name  |type        | phone
-------------------------------
john  | developer  | 100
steve | manager    | 102

Venn Diagram illustrates result very well.

left outer join

select * from workers left outer join phones on workers.name = phones.name

output:

-------------------------------
name  |type         | phone
-------------------------------
john  | developer   | 100
steve | manager     | 102
bruce | seller      | null
neal  | architect   | null

The result involves, as you can see, list of all workers.

right outer join

A query with this type of join works similarly to the previous query, but there is only difference is that the result contains all rows of the second table.

select * from workers right outer join phones on workers.name = phones.name

-------------------------------
name   |type         | phone
-------------------------------
john   | developer   | 100
steve  | manager     | 102
jeramy | null        | 103
zach   | null        | 101

inner join and right inner join

The result of executions of these queries with inner join, right inner join and left inner join are identical.

select * from workers inner join phones on workers.name = phones.name

-------------------------------
name  |type        | phone
-------------------------------
john  | developer  | 100
steve | manager    | 102

full outer join

Beside basic types, there are several additional types. They are used rarely, but sometimes you can find them useful. One of them is full outer join.

select * from workers full outer join phones on workers.name = phones.name

After execution the query abow, we will have rows from the results of left outer join and right outer join.

-------------------------------
name   |type         | phone
-------------------------------
john   | developer   | 100
steve  | manager     | 102
jeramy | null        | 103
zach   | null        | 101
bruce  | seller      | null
neal   | architect   | null

Moreover, we can use where for the query and get rows that do not match.

select * from workers full outer join phones on workers.name = phones.name where phones.phone is null or workers.type is null;

The result:

-------------------------------
name   |type         | phone
-------------------------------
jeramy | null        | 103
zach   | null        | 101
bruce  | seller      | null
neal   | architect   | null

cross join

It is one of specific type of joins. The result of execution involves all possible combinations of rows from two tables. It is called Cartesian product. The count of the result equals a product of count of rows of both tables.

select * from workers cross join phones

others

This part is about some useful queries with different types of joins.

If we can get a list of workers who do not have phone numbers, we can use the query below:

select * from workers left outer join phones where phones.phone is null

The result:

-------------------------------
name   |type         | phone
-------------------------------
bruce  | seller      | null
neal   | architect   | null

You can get a set of workers who do not work and who have a phone number with the next query:

select * from workers right outer join phones where workers.type is null

The result:

-------------------------------
name   |type         | phone
-------------------------------
jeramy | null        | 103
zach   | null        | 101

That is all what I want to share with you. I hope the article will come in handy.

Visualization of join types