This video discusses how to combine two sets of results together in SQL. The following SQL keywords are covered:
UNION,
UNION ALL,
INTERSECT,
MINUS, and EXCEPT.
Embed this video using the code below
Video Transcription
In this tutorial, we look into how to combine two sets of results in SQL. There are 3 ways to do this. The first one is similar to an "AND" operation, whereby only rows that are common in both sets are selected. The second one is similar to an "OR" operation, where all rows that appear in either set are selected. The third one is taking only the rows in Set 1 that do not appear in Set 2.
Each of the three ways have corresponding commands in SQL. The first one is achieved via the "INTERSECT" command. The second is achieved via the "UNION" or "UNION ALL" command. The third one is achieved via the "MINUS" or "EXCEPT" command. The difference between UNION and UNION ALL is that UNION only shows distinct values, and UNION ALL shows all rows. So, if a value appears 4 times in Set 1 and Set 2 combined, the UNION command would return that value once, while the UNION ALL command would return that value 4 times. The MINUS and EXCEPT commands are the same. Some databases use MINUS while other databases use EXCEPT.
Let's take a look at an example and see how they are different. Assuming Set 1 has 4 rows: John, Larry, Mary, and Sophie, and Set 2 has 3 rows: John, Angela, and Linda. When we use the INTERSECT command, the result is John. When we use the UNION command, the result is John, Larry, Mary, Sophie, Angela, and Linda. When we use the UNION ALL command, the result is John, Larry, Mary, Sophie, John, Angela, and Linda. When we use the MINUS or EXCEPT command, the result is Larry, Mary, and Sophie.
