MySQL Join

The relational database system needs to interconnect multiple tables with each other. MySQL is a popular and easy data management system to connect multiple tables. The foreign key is used to connect one table with another table using a similar column. MySQL Joins remove the drawback of a foreign key.

Join in MySQL helps to connect either a single table or multiple tables using matching columns. There are several types to joins columns of the tables. The join types are depended on the table columns and their position. Join operation helps to improve table data and its format. The join works on the command-line client interface and workbench interface both. The command-line interface uses a query to perform join operation in MySQL.

Types of MySQL JOIN

The basic MySQL join operations are inner join, left join, and right join. It also support the cross joins to works with complex operation. The following table illustrates the MySQL join types and their descriptions:

MySQL Joins	Description
Inner join	MySQL inner join returns the record by connecting common columns of the two tables. It is the basic join of the MySQL system.
Left join	MySQL left join interconnects the two tables with each other. The right table column connects with the complete left side table.
Right join	MySQL right join interconnects the two tables with each other. The left table column connects with the complete right side table.
Cross join	The cross join connects any column of the multiple tables. Thus, this join interconnects multiple columns in multiple tables.
Self Join	The table column interconnects with itself. Thus, the single table connects its column.

Operations of the MySQL join

The following table shows join operation and its description. You can create, update, and delete joins using the "join" keyword.

Join Operation	Description
Create join	Create required join type as per system requirement.
Update join	Modify and operate join in the single or multiple tables.
Delete Join	Delete the unwanted joins in the table.
Natural join	The natural join combines multiple tables using a column with the same name and data type.

Uses of the MySQL join

• MySQL join retrieves data from multiple tables.
• The join removes the drawback of the foreign key connection.
• It helps to operate large-scale data of the application.
• It can join the single or multiple tables.

Syntax of the MySQL types

• The join syntax are given below.

MySQL expression Table1 JOIN table2 condition;

• The inner join syntax are given below.

MySQL expression Table1 INNER JOIN table2 condition;

• The left join syntax are given below.

MySQL expression Table1 LEFT [OUTER] JOIN table2 condition;

• The right join syntax are given below.

MySQL expression Table1 RIGHT [OUTER] JOIN table2 condition;

Prerequisite for the MySQL join

• Select the required database for the MySQL table.
• Create the first table with the required columns.

CREATE TABLE name1 (
Column1 data type constraint,
Column2 data type constraint);

• Insert data into the first table.

Insert into table name (column list) values (values);

• Create a second table with the required columns.

CREATE TABLE name1 (
Column1 data type constraint,
Column2 data type constraint);

• Insert data into the second table.

Insert into table name (column list) values (values);

Example

Let us understand the join operation with the below example.

• Create first table named department with the required columns.

mysql> CREATE TABLE department (
    	department_id INT AUTO_INCREMENT,
    	department_name VARCHAR(45),
    	admissions INT,
    	PRIMARY KEY (department_id));

• Insert data into the department table. Here, the table includes the department name and number of the admissions.

mysql> INSERT INTO department (department_name, admissions) VALUES("Bachelor of Arts", 45), ("Bachelor of Commerce", 55), ("Bachelor of Engineering", 50), ("Bachelor of Technology", 60);

• Create a second table named subject with the required columns.

mysql> CREATE TABLE subject (
subject_id INT AUTO_INCREMENT,
subject_name VARCHAR(45),
students INT,
PRIMARY KEY (subject_id));

• Insert data into the subject table. Here, the table includes the subject name and number of the students.

mysql> INSERT INTO subject (subject_name, students) VALUES("Electronics", 20), ("Electrical", 25), ("Computer Science", 15), ("Information Technology", 30);

• You can see the output of the department table structure and its data using the below query.

mysql> SELECT * FROM department;

In the above image, you can see that the table contains department id, department name, and the number of the admissions.

• You can see the output of the subject table structure and its data using the below query.

mysql> SELECT * FROM subject;

In the above image, you can see that the table contains the subject id, subject name, and the number of the student.

Difference between inner join, natural join, and Equi join

The following table shows the main differences between the multiple joins.

Inner join	Natural join	Equi join
The inner join connects several tables using columns based on the "ON" clause.	The natural join connects several tables using the same column names and their data type.	The equi join connects the table using similar or equal columns and the associate table.
This join retrieves the column using the match column as per the ON condition.	This join retrieves unique columns of the various tables.	This join retrieves column with is equal or match as per join condition.
The inner join syntax shows below. SELECT Column1 data type constraint, Column2 data type constraint, FROM table name1 INNER JOIN table2 ON condition1;	The natural join syntax shows below. SELECT *FROM table name1 NATURAL JOIN table2 ON condition1;	The equi join syntax shows below. SELECT * FROM table1 JOIN table2 ON table1.Column = table2.Column;

Comparison between union and join

The following table shows similarities and differences between joins and union.

Joins	Union
The joins use to retrieve table records from several tables.	The union uses for combines the output data of the multiple tables.
The union works on data using a new column.	The union works on data using new columns.
The syntax of the MySQL union shows below.	The syntax of the MySQL union below.
The join has five types. It uses inner join, left join, right join, cross join, self join.	The union has two types. It commonly uses the "union" and "union all" types.