What are the different types of SQL JOIN clauses?
SQL is a querying language being used through out almost every industry. It’s a must know language if you are going to remain professionally competitive. I was catching up with a friend who works in project management down in DC and she found herself on numerous occasions having to pick up SQL for her clients. Her background was not in Data, but it was a skill she had to pick up on the job.
As I continue my networking I’m finding that many professionals from a non-data analytical background have had to pick up SQL on their own. Start ups and companies use databases, and if you’re looking to take a step into an analyst job, you’re going to need to learn how to access their databases.
If you’re working with a relational database you can imagine yourself working within MySQL and needing to access a variety of different tables within your database.
One key clause that will help you work with several different tables within your database is the JOIN clause. If you’re about to set foot into data analytics, definitely get some practice and familiarity with the different types of joins. I’ve taken the time to distinguish sample queries and Venn Diagrams that properly illustrate every kind of JOIN.
When writing a JOIN clause within your query you must remember several details and steps.
I recommend starting off with the pseudocode so you can map out where you will be connecting your tables. This will be extremely helpful when you are working with nested subqueries and joining on multiple tables.
I will briefly describe each type of JOIN below but when joining, it is important to understand which column you are joining on. For that, it’s highly recommended that you familiarize yourself with the primary and foreign keys within your database tables. These keys will be what you will be joining your tables on.
As you write out your query don’t forget to alias your tables so that your tables so that your query is understandable to a third party. This will come especially handy when you are working with nested subqueries.
LEFT JOIN
“The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.”
In the case of a left exclusive JOIN you are essentially trying to query out the unique values that exist in the left table. In other words, you don’t want any of the right table’s values, you solely want the left table’s values.
RIGHT JOIN
“The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.”
In the case of a right exclusive JOIN you are essentially trying to query out the unique values that exist in the left table. In other words, you don’t want any of the right table’s values, you solely want the left table’s values.
INNER JOIN
“The INNER JOIN keyword selects records that have matching values in both tables.”
OUTER JOIN
“The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.”
In the case of a full outer exclusive JOIN, you are essentially selecting all unique values from both the left and right tables, by excluding the common values in both tables.
Resources to Learn SQL
As a professional in an evolving data analytics atmosphere, it’s important to understand a basic clause like JOIN. I recommend taking some time to do some practice on Hackerrank, Leetcode, and Mode. I’ve been touching up my experience with these helpful tools and I feel like I learn something new with every new coding challenge. Additionally I recommend O’Reilly’s Learning SQL, it’s a great book to familiarize yourself with databases and SQL querying.
Feel free to connect with me on LinkedIn if you found this helpful or if you’re looking for data analytics professionals on your team, I’d love to chat. Hope this blog was educational!
