Basic DB Using SQL Server (Part 4) - SQL JOIN
Tags: sql, database
Published: 2020-02-16 15:26:30

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

1. [INNER JOIN](#inner-join) 
2. [OUTER JOIN](#outer-join)
    1. [LEFT OUTER JOIN](#l-outer-join)
    2. [RIGHT OUTER JOIN](#r-outer-join)
    3. [FULL JOIN](#full-join)
3. [CROSS JOIN](#cross-join)
4. (bonus) [UNION](#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.) 

<a name="inner-join"></a>
# 1) INNER JOIN 

It is the most common kind of `JOIN` which returns only the records where there are matches.

![inner join](https://dev-to-uploads.s3.amazonaws.com/i/olb45pupao4zq2pnyayb.png)

``` sql
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`**:  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/fu7j36vzbhvp7btr965o.png)

Table **`Actors`**:  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/2qdj412b1tqr2eed8idc.png)

If we run the script below
``` sql
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  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/tcbomndxvek43c2nqtfz.png)

<sub>NOTE: Alias is a temporary name we call our table, so we don't have to write the full table name. It is optional.</sub>

<a name="outer-join"></a>
# 2) OUTER JOIN 

There are 3 kinds of `OUTER JOIN`; `LEFT JOIN`, `RIGHT JOIN`, and `FULL JOIN`.

``` sql
-- syntax --
SELECT <select_list>
FROM <first_table>
<LEFT | RIGHT | FULL> JOIN
<second_table>
ON <join_condition>
```

<a name="l-outer-join"></a>
### 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. 

![left join](https://dev-to-uploads.s3.amazonaws.com/i/3206psb1aihd8m0wiq8t.png)

Using the same `Films` and `Actors` tables, if we run the script below
``` sql
SELECT * 
FROM Actors a LEFT INNER JOIN Films f
ON a.filmId = f.filmId
```

we will get  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ve1x5ylgf4z99pin4ejc.png)

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`. 

<a name="r-outer-join"></a>
### 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. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/csz575vs5k9hqay8lwbx.png)

Again, using the same `Films` and `Actors` tables, if we run the script below
``` sql
SELECT * 
FROM Films f RIGHT JOIN Actors a
ON a.filmId = f.filmId
```

we will get  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/7s8c17euhv0wdtr738k5.png)

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
``` sql
SELECT * 
FROM Actors a RIGHT JOIN Films f
ON a.filmId = f.filmId
```
we will get a different result like this  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/iufrso4taezznrxyhvns.png)

Instead of getting `Humphrey Bogart`, we get `NULL` cells and `filmId = 3`'s Amelie which only exists in the `Films` table.

<a name="full-join"></a>
### 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`. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/eyxdc5jrxk3j1j6ivfiw.png)

If we run the script below
```sql
SELECT * 
FROM Actors a FULL JOIN Films f
ON a.filmId = f.filmId
```

we will get

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/32amodcgxy6ythi4artn.png)

<a name="cross-join"></a>
# 3) CROSS JOIN 

The result of `CROSS JOIN` is a *Cartesian product* of all records on both sides of the `JOIN`.

![cross join](https://dev-to-uploads.s3.amazonaws.com/i/5hnr28vapi9yha2fryro.png)

If we run the script below using the same tables 
```sql
SELECT * 
FROM Actors, Films 

-- or you can use the alternative script below --
SELECT * 
FROM Actors CROSS JOIN Films
```
we will get  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/2ydv2cng2az1q66zgyta.png)

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. 

<a name="union"></a>
# 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
``` sql
-- 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.  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/1myynctt01zuocwhuuxn.png)

However, if we run the script below
``` sql
-- first query --
SELECT filmId, filmName
FROM Films
UNION ALL
-- second query --
SELECT filmId, firstName
FROM Actors
```
we will get  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/5u3f1ivcaoir8hcjavrx.png)

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](https://github.com/imrinzzzz/year3-database-lab/blob/master/lab4/AdventureWorks.zip) that you can used to create the `AdventureWorks2014` database! Some exercise to test your knowledge can be found [here](https://github.com/imrinzzzz/year3-database-lab/blob/master/lab4/JOIN-exercise.md) and the SQL queries to the exercise can be found [here](https://github.com/imrinzzzz/year3-database-lab/blob/master/lab4/JOIN.sql)!

Hope you have a good day~