SQL is often the language of choice for storing, manipulating, and retrieving data from a database for data science projects. A SQL OUTER JOIN is a command that makes handling data much more efficient. And because of this SQL interview questions are common in data science interviews.
In SQL, you can use an INNER JOIN to pull common rows between two tables. But what if you want to get the unique rows or the rows that are not common between the two tables?
In these cases, you need the opposite of an INNER JOIN, which is the OUTER JOIN. Let’s take a closer look at how to use the OUTER JOIN.
The INNER JOIN command extracts the intersection between two tables.
INNER JOIN syntax SELECT colums FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
The OUTER JOIN does the opposite, and extracts the rows unique to only one table. To do that, you can use two JOIN commands, OUTER and RIGHT/ LEFT, depending on which table we want to extract unique rows from.
The OUTER JOIN command returns the rows from both tables.
OUTER JOIN syntax SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
The FULL keyword here indicates that we want all rows in both tables.
Pulling unique rows from one table without rows that exist in both tables is an intermediate SQL task. One way to do that is to get the rows from the table we want, and then filter the results to remove any rows in the other table using the WHERE keyword.
To pull unique rows, start with an OUTER JOIN command with the LEFT or RIGHT keywords.
Here is the syntax to use for extracting unique values from the left table:
SELECT t1.* FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.id=t2.id WHERE t2.id IS null
You can do the same but with the right table by changing the WHERE command’s condition in the last line and using the RIGHT JOIN command instead of LEFT.
Here is the syntax to use for extracting unique values from the right table:
SELECT t1.* FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.id=t2.id WHERE t1.id IS null
The best way to get better at data manipulation in SQL is practice. Interview Query offers a variety of SQL learning resources to help you practice and improve your SQL skills, including: