Basic DB Using SQL Server (Part 4) - SQL JOIN

Hey there! This is another post about SQL server. This post is about SQL JOIN. Today's topic includes:

1. INNER JOIN
2. OUTER JOIN
   1. LEFT OUTER JOIN
   2. RIGHT OUTER JOIN
   3. FULL JOIN
3. CROSS JOIN
4. (bonus) UNION

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>

Table Films:

Table Actors:

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

we will get

_{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

we will get

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

we will get

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

we will get

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 the SELECT 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 is DISTINCT rather than ALL.

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

we will get

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~