JOIN clause in SQL

The JOIN clause is extremely important in SQL language. The JOIN clause joins rows from two or more tables. That being said, let’s move straight to the examples and see what exactly the JOIN clause does!

Note:
You need my MySQL installed on your local machine.
You need Chinook database as well since the examples are based on Chinook (see this link for getting started http://dan.saigon.ro/mysql-and-sql-getting-started/).

As usual, start the server, connect to the server, display the databases, select Chinook, and show its tables.

service mysql start
mysql -u root -p

mysql> show databases;
mysql> use Chinook;
mysql> show tables;

If everything went well then you should have something like this:

mysql> show tables;
+-------------------+
| Tables_in_Chinook |
+-------------------+
| Album             |
| Artist            |
| Customer          |
| Employee          |
| Genre             |
| Invoice           |
| InvoiceLine       |
| MediaType         |
| Playlist          |
| PlaylistTrack     |
| Track             |
+-------------------+
11 rows in set (0.00 sec)

The following challenge was given in the classroom during my time at the coding bootcamp at Code Institute in Dublin.

Here is what the challenge says:

/*
Join the 'Track' table and the 'MediaType' table to create a query that shows the Name of the Track, and the Name of the Media Type. Both tables have a 'MediaTypeId' column that you can join on. Both tables also have 'Name' columns, so you'll need to use aliases

Expected: 3503 rows (Here's a sample, actual tracks may be different)
For Those About To Rock (We Salute You) MPEG audio file
Balls to the Wall Protected AAC audio file
Fast As a Shark Protected AAC audio file
*/

Let’s take the challenge and analyze it piece by piece. First it says “Join the ‘Track’ table and the ‘MediaType’ table…”. I would stop here and run a desc command on these two tables.

mysql> desc Track;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| TrackId      | int(11)       | NO   | PRI | NULL    | auto_increment |
| Name         | varchar(200)  | NO   |     | NULL    |                |
| AlbumId      | int(11)       | YES  | MUL | NULL    |                |
| MediaTypeId  | int(11)       | NO   | MUL | NULL    |                |
| GenreId      | int(11)       | YES  | MUL | NULL    |                |
| Composer     | varchar(220)  | YES  |     | NULL    |                |
| Milliseconds | int(11)       | NO   |     | NULL    |                |
| Bytes        | int(11)       | YES  |     | NULL    |                |
| UnitPrice    | decimal(10,2) | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> desc MediaType;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| MediaTypeId | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name        | varchar(120) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

They both have a MediaTypeId column that we can join on.

Let’s continue analyzing the text of the challenge. It says “to create a query that shows the Name of the Track, and the Name of the Media Type …”.

So, for the beginning, I will go like this:

SELECT Track.Name as Track, MediaType.Name as MediaType;

All of the above say that I want the Name field from the Track tale and the Name field from the MediaType table. Since they are both named Name, the as renamed them in the output as Track and MediaType respectively.

In order to display these two columns we have to join the tables. So, let’s do it!

mysql> SELECT Track.Name AS Track, MediaType.Name AS MediaType
FROM Track
JOIN MediaType
ON Track.MediaTypeId = MediaType.MediaTypeId
LIMIT 10;

The last line, LIMIT 10 just limits the result to 10 lines. Normally we should have 3503 rows!

The result is shown below:


+-----------------------------------------+-----------------+
| Track                                   | MediaType       |
+-----------------------------------------+-----------------+
| For Those About To Rock (We Salute You) | MPEG audio file |
| Put The Finger On You                   | MPEG audio file |
| Let`s Get It Up                         | MPEG audio file |
| Inject The Venom                        | MPEG audio file |
| Snowballed                              | MPEG audio file |
| Evil Walks                              | MPEG audio file |
| C.O.D.                                  | MPEG audio file |
| Breaking The Rules                      | MPEG audio file |
| Night Of The Long Knives                | MPEG audio file |
| Spellbound                              | MPEG audio file |
+-----------------------------------------+-----------------+
10 rows in set (0.00 sec)

That is all to extract information from two different tables and display it in a new table.

Leave a Reply