JOIN
Overview
JOIN
clause is used to create a new table by combining records and using common fields between two tables in a database.
JOIN
clause.Syntax
a) Basic Syntax
The following is the syntax of the JOIN
clause:
SELECT table_1.column_1, table_2.column_2...
FROM table_1
JOIN table_2
ON table_1.common_filed = table_2.common_field
-
SELECT table_1.column_1, table_2.column_2...
will select the columns to be displayed from both tables. -
FROM table_1 JOIN table_2
represents the joined tables. -
ON table_1.common_filed = table_2.common_field
compares each row of table_1 with each row of table_2 to find all pairs of rows that meet the join-common field. -
When the join-common field is met, column values for each matched pair of rows from table_1 and table_2 are combined into a result row.
b) Syntax with an Alias
You can use table aliasing to refer to the table’s name. An alias is a temporary name given to a table, column, or expression in a query.
The results will stay the same, but it can help you to write the query easier.
SELECT left.column_1, right.column_2...
FROM table_1 as left
JOIN table_2 as right
ON left.common_filed = right.common_field
Examples
Before we move on, let us assume two tables:
movies table
CREATE TABLE movies (
movie_id int,
movie_name string,
category_id int
);
INSERT INTO movies
(movie_id, movie_name, category_id)
VALUES
(201011, 'The Avengers', 181893),
(200914, 'Avatar', 181894),
(201029, 'Shutter Island', 181891),
(201925, 'Tune in Your Love', 181892);
SELECT * FROM movies;
It will create a table as shown below:
+------------+-----------------------+--------------+
| movie_id | movie_name | category_id |
+------------+-----------------------+--------------+
| 201011 | The Avengers | 181893 |
| 200914 | Avatar | 181894 |
| 201029 | Shutter Island | 181891 |
| 201925 | Tune in Your Love | 181892 |
+------------+-----------------------+--------------+
categories table
CREATE TABLE categories (
id int,
category_name string
);
INSERT INTO categories
(id, category_name)
VALUES
(181891, 'Psychological Thriller'),
(181892, 'Romance'),
(181893, 'Fantasy'),
(181894, 'Science Fiction'),
(181895, 'Action');
SELECT * FROM categories;
It will create a table as shown below:
+--------------+-----------------------+
| id | category_name |
+-----------+--------------------------+
| 181891 | Psychological Thriller |
| 181892 | Romance |
| 181893 | Fantasy |
| 181894 | Science Fiction |
| 181895 | Action |
+-----------+--------------------------+
- Based on the above tables, we can write a
JOIN
query as follows:
SELECT a.movie_name, c.category_name
FROM movies AS a
JOIN categories AS c
ON a.category_id = c.id;
- The above query will give the following result:
+-----------------------+---------------------------+
| movie_name | category_name |
+-----------------------+---------------------------+
| Shutter Island | Psychological Thriller |
| Tune in Your Love | Romance |
| The Avengers | Fantasy |
| Avatar | Science Fiction |
+-----------------------+---------------------------+
The JOIN checks each row of the category_id column in the first table (movies) with the value in the id column of each row in the second table (categories).
If the values are equal, it will create a new row that contains columns from both tables (category_name) and adds the new row (movie_name) to the result set.
Below is the Venn diagram based on the example: