For SQL learners, it is important to understand the concepts and differences between several JOIN types. A SQL JOIN statement is used to combine data and rows from two or more tables based on a common field between them. Let’s start with a classical venn diagram from C.L. Moffat, which clearly show the differences between a set of JOINs.
Here I summarized the most popular JOINs’ syntax and visual in three kinds of SQL systems. They are all pretty similar. One obvious difference is that MySQL does not support FULL (OUTER) JOIN. It is a bomber, but we have ways to access to the same data, which is included in the Common Questions #5 later.
| Visual Representation | MySQL | Oracle PL/SQL | T-SQL (e.g. MS SQL server |
 | (INNER) JOIN | (INNER) JOIN | (INNER) JOIN |
 | LEFT (OUTER) JOIN | LEFT (OUTER) JOIN | LEFT (OUTER) JOIN |
 | RIGHT (OUTER) JOIN | RIGHT (OUTER) JOIN | RIGHT (OUTER) JOIN |
 | N/A | FULL (OUTER) JOIN | FULL (OUTER) JOIN |
 | (CROSS) JOIN | CROSS JOIN | CROSS JOIN |
Common Q&A
When I am practicing SQL questions, I have the following questions. I believe many beginners have similar questions too and I hope this Q&A summarization can help out.
- When selecting from multiple tables, WHERE clause and JOIN syntax can do similar tasks. When should I use WHERE clause, and when should I use JOIN syntax?
We can regard WHERE clause as a kind of INNER JOIN. The WHERE clause defines what condition the columns of two tables should meet. Let’s take a quick sample here.
Let’s say I want to have the name of employees and the cities where they work. INNER JOIN and WHERE clause can get me the same results, with similar running duration.
However, WHERE clause can only return the matching rows. If the selected column has NULL value or unmatched value, and at the same time, you want to show all the results from a certain table even there is no matching rows from another table, you have to use LEFT JOIN or RIGHT JOIN instead.
For example, we want to match employees and their direct manager. In this case, WHERE clause only returns those matching rows (22 rows in total) because President has no direct manager. If you want to show all the results (23 rows in total), then LEFT JOIN is the right option.
Therefore, I usually use JOIN syntax and avoid using WHERE clause because different types of JOINs can retrieve desired results more specifically.
2. Is INNER JOIN equal to JOIN?
3. Is LEFT OUTER JOIN equal to LEFT JOIN?
Yes, they are equivalent. This is called ‘syntactic sugar’, which is designed to make things easier to read or to express. When you are familiar with how to use different kinds of JOINs, you can start simplify your syntax that reach the same goal.
4. Does JOIN represent INNER JOIN or CROSS JOIN?
In MySQL, JOIN is the concise syntax (so called syntactic sugar) both for INNER JOIN and CROSS JOIN. However, when using CROSS JOIN, you can not add ON (condition) statement because it’s supposed to get the product of two tables. Instead, when using INNER JOIN, an ON (condition) statement is a must.
5. If MySQL does not support FULL OUTER JOIN, what syntax should I use to get the same results?
We can emulate a full outer join by doing a UNION of a LEFT JOIN and a RIGHT JOIN.
Let’s take a look at an example: There is a list of students who select physics class and another list of students who select chemistry class. Let’s say I want to know who select both classes, and who only select only one class (and which class does he/she selects).
Table: physics
Table: chemistry
Target table: students list who registered in physics and chemistry classes
First, we use LEFT OUTER JOIN to choose those students who selected physics class and if they chooses chemistry class or not.
Next, use RIGHT OUTER JOIN to choose those students who selected chemistry class and whether they chooses physics class or not.
Finally, use UNION to combine all the rows from two tables to reveal all students who attended chemistry and physics classes.
In conclusion, the more you practice SQL questions, the more you are familiar with these different kinds of JOINs. I always believe in a proverb ‘I hear and I forget. I see and I remember. I do and I understand’. I’ll share my experience practicing SQL Leetcode later!