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:
FULL OUTER JOIN will combine all those records above in one table with results:
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.
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.
- 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:
So if you use LEFT OUTER JOIN (or LEFT JOIN in other naming) - you will receive results like this:
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:
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.