OUTER JOINS tutorial - this is how to use it

In the previous article we were talking about INNER JOINS. Today we will try to understand OUTER JOINS with all their types.

If you want to understand how they work - this is place for you, just click Read more :)

• FULL OUTER JOIN

FULL OUTER JOIN will just simply get all data from both tables you're joining in.

Let's say, you have two tables:

users table

id	name	customer_id
1	John	5
2	Adam	6
3	Robert	7

customers table

id	saldo	active
7	100	1
8	150	0
9	0	0

FULL OUTER JOIN will combine all those records above in one table with results:

id	name	customer_id	id	saldo	active
1	John	5	NULL	NULL	NULL
2	Adam	6	NULL	NULL	NULL
3	Robert	7	7	100	1
NULL	NULL	NULL	8	150	0
NULL	NULL	NULL	9	0	0

As you can see - if some results won't match between tables, it will be filled automatically with NULL value.

The query for this use will look like this:

SELECT * FROM users FULL OUTER JOIN customers ON users.customer_id = customers.id

• FULL OUTER JOIN with WHERE clause

If you combine FULL OUTER JOIN with WHERE clause - you can receive opposite results to the INNER JOIN clause. This is very useful, when you want to receive some data that are not matched between two tables.

Example:

SELECT * FROM users 
FULL OUTER JOIN customers 
ON users.customers_id = customers.id 
WHERE users.id IS null OR customers.id IS null

This query will filter out queries where id of both tables are matched.

id	name	customer_id	id	saldo	active
1	John	5	NULL	NULL	NULL
1	Adam	6	NULL	NULL	NULL
NULL	NULL	NULL	8	150	0
NULL	NULL	NULL	9	0	0

• LEFT OUTER JOIN

LEFT OUTER JOIN clause is the one, which makes a lot of problem for beginners. Basically we are taking now all the data from the left table.
If some rows are matching in the right table - we are combining this data. If there is no data in the right table that matches left table, we just
fill this side with null values.

Let's look at those tables again:

users table

id	name	customer_id
1	John	5
2	Adam	6
3	Robert	7

customers table

id	saldo	active
7	100	1
8	150	0
9	0	0

So if you use LEFT OUTER JOIN (or LEFT JOIN in other naming) - you will receive results like this:

id	name	customer_id	id	saldo	active
1	John	5	NULL	NULL	NULL
2	Adam	6	NULL	NULL	NULL
3	Robert	7	7	100	1

As you can see. We just copied left table, and combine it with matching records in the right table. If we didn't find matching data,
we filled it up with null value.

Query, we have used:

SELECT * FROM users LEFT OUTER JOIN customers
ON users.customer_id = customers.id

Please, remember that the order in the LEFT OUTER JOIN clause does matter.

• LEFT OUTER JOIN with WHERE statement

What if we want to filter out from users table records, that do not have matching record in the customers table?

We can combine our clause with WHERE statement again.

SELECT * FROM users LEFT OUTER JOIN customers
ON users.customer_id = customers.id
WHERE customers.id IS null

This will give us results like below:

id	name	customer_id	id	saldo	active
1	Adam	5	NULL	NULL	NULL
2	Robert	6	NULL	NULL	NULL

Basically we get users which does not have customer record. :)

• RIGHT JOIN and RIGHT JOIN with WHERE statement

RIGHT JOIN is basically the same thing as LEFT JOIN, it just uses right table as base and matching it with the left table.

You can use RIGHT JOIN or just switch tables order inside your LEFT OUTER JOIN clause.