Hey there! This is another post about SQL server. This post is about SQL JOIN. Today's topic includes:
JOIN
is an SQL command that lets you connect information from 2 or more tables into one result through key attributes (depends on how user tell JOIN
to put the data together.)
1) INNER JOIN
It is the most common kind of JOIN
which returns only the records where there are matches.
SELECT <select_list>
FROM <first_table> INNER JOIN <second_table>
ON <join_conditions>
-- or you can just use 'JOIN' (automatically means INNER JOIN) --
SELECT <select_list>
FROM <first_table> JOIN <second_table>
ON <join_conditions>
If we run the script below
SELECT *
FROM Films f INNER JOIN Actors a
-- the 'f' and 'a' is an alias of the tables to shorten the name --
ON f.filmId = a.filmId
NOTE: Alias is a temporary name we call our table, so we don't have to write the full table name. It is optional.
2) OUTER JOIN
There are 3 kinds of OUTER JOIN
; LEFT JOIN
, RIGHT JOIN
, and FULL JOIN
.
-- syntax --
SELECT <select_list>
FROM <first_table>
<LEFT | RIGHT | FULL> JOIN
<second_table>
ON <join_condition>
i. LEFT OUTER JOIN
A LEFT JOIN
includes all the information from the table on the LEFT plus the matched information from the right table.
Using the same Films
and Actors
tables, if we run the script below
SELECT *
FROM Actors a LEFT INNER JOIN Films f
ON a.filmId = f.filmId
As you can see, in the table Actors
, we have a record with filmId = 5
. However, in the table Films
, we don't have a record with filmId = 5
. The result of joining these 2 using LEFT JOIN
, we get NULL
for the empty record from Actors
.
ii. RIGHT OUTER JOIN
A RIGHT JOIN
includes all the information from the table on the RIGHT plus the matched information from the left table. Basically, it is a reverse of LEFT JOIN
. You can use these 2 interchangeably with the correct order of the selected tables.
Again, using the same Films
and Actors
tables, if we run the script below
SELECT *
FROM Films f RIGHT JOIN Actors a
ON a.filmId = f.filmId
As you can see, I swapped the order between Films
and Actors
tables, so all information from the Actors
table are included (just like what we did in the LEFT JOIN
.)
If we were to swap the order of the 2 tables just like the script below
SELECT *
FROM Actors a RIGHT JOIN Films f
ON a.filmId = f.filmId
we will get a different result like this
Instead of getting Humphrey Bogart
, we get NULL
cells and filmId = 3
's Amelie which only exists in the Films
table.
iii. FULL OUTER JOIN
FULL JOIN
include all the information from both sides of the table. It can be said that the result after doing LEFT JOIN
and RIGHT JOIN
in the same as doing FULL JOIN
.
If we run the script below
SELECT *
FROM Actors a FULL JOIN Films f
ON a.filmId = f.filmId
we will get
3) CROSS JOIN
The result of CROSS JOIN
is a Cartesian product of all records on both sides of the JOIN
.
If we run the script below using the same tables
SELECT *
FROM Actors, Films
-- or you can use the alternative script below --
SELECT *
FROM Actors CROSS JOIN Films
Since we have 3 records from the Films
table and 4 records from the Actors
table, using CROSS JOIN
, we get 3*4 or 12 records as a result.
4) UNION
UNION
is a special operator used to append data from one query onto the end of another query.
-
JOIN
combines information horizontally (adding more columns) -
UNION
combines data vertically (adding more rows)
However, there are a few key points about UNION
to note.
- All the
UNION
queries must have the same number of columns in theSELECT
list. - The data types of each column in a query must be implicitly compatible with the data type of the same column from another query.
- The default return option for
UNION
isDISTINCT
rather thanALL
.
Let's look at some examples
-- first query --
SELECT filmId, filmName
FROM Films
UNION
-- second query --
SELECT filmId, firstName
FROM Actors
We are combining 2 queries together using UNION
. The result of this is.
However, if we run the script below
-- first query --
SELECT filmId, filmName
FROM Films
UNION ALL
-- second query --
SELECT filmId, firstName
FROM Actors
The result will contain dulicate(s) if we use UNION ALL
instead of just UNION
.
EOF !
And that's the end of part 4! There is a script file that you can used to create the AdventureWorks2014
database! Some exercise to test your knowledge can be found here and the SQL queries to the exercise can be found here!
Hope you have a good day~
Top comments (0)