JOIN
This article will teach you more about the JOIN clause, its syntax, and how it works.
Overview
JOIN clause is used to create a new table by combining records and using common fields between two tables in a database.
✅ We support table aliasing used in the JOIN clause.
Syntax
a) Basic Syntax
The following is the syntax of the JOIN clause:
- 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 table2_ 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.
Examples
Before we move on, let us assume two tables:
movies table
It will create a table as shown below:
categories table
It will create a table as shown below:
1) Based on the above tables, we can write a JOIN query as follows:
2) The above query will give the following result:
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:
