Day 14: FULL OUTER JOIN In SQL
Day 14 Of Learning
Hey Guys, hope you’re good!
Whats up with everyone? How’s the learning going?
Today we’ll be looking at a FULL OUTER JOIN and its functionality in SQL.
So, let’s get started.
Use Of FULL OUTER JOIN In SQL
A FULL OUTER JOIN basically combines all the matched/unmatched rows in the tables being joined. It combines irrespective of matches. So if I had a table employees and salaries and I execute a FULL OUTER JOIN between them all rows from both the tables without NULLs would be displayed in the output.
So, lets get started with our restaurant dataset. I’ll be using the same order_details2 table and the menu_items table from the previous post.
Note: FULL OUTER JOIN does not work in MySQL so I’ll be using the indirect method to execute a FULL OUTER JOIN in MySQL. Watch attentively.
I added another value to show you the function of a FULL OUTER JOIN. So we first executed a left join and then a right join and used union to execute the query together and without duplicates. So as we can see that the FULL OUTER JOIN has not omitted any rows based on their matches.
However when I execute a RIGHT JOIN like I did yesterday, I get this output.
The item_id 200 is not available in the menu_items table and hence no match is made, so the RIGHT JOIN has omitted the value.
I hope you get it.
Moving on.
Databases that Support FULL OUTER JOIN
PostgreSQL
SQL Server
Oracle
IBM Db2
Snowflake
Exercise
Here’s what you need to do today:
Load the Hospital Records dataset
Execute a FULL OUTER JOIN like the way I did if you’re using MySQL
Comment if you got the complete data from both the tables.
If you have any confusion or doubts you can comment below and I’ll help you.
I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:
ajmal7809@gmail.com
bideveloper_ (Discord)