MySQL EQUI JOIN

An equijoin is an operation that combines multiple tables based on equality or matching column values in the associated tables. This operation links more than two tables based on a similar column or join. The equi join process uses the "WHERE" clause with equal (=) regular expression. In addition, this operation uses the "ON" clause to identify the table column and respective secondary table.

Rule of the equijoin

• MySQL equi join does not need the same name columns for equal expression.
• The multiple time's same columns can be displayed in the resultant result.
• You can work on more than two tables for equi join operation.
• The table does not display the null value in the column.
• The table does not display the unmatched value of the tables.
• The equal sign is necessary for comparison operation.
• The equi join recommends ON and WHERE clause after JOIN statement.

Syntax

The "equi join" with the "WHERE" clause syntax shows below.

SELECT Column1 data type constraint,
Column2 data type constraint,
Column3 data type constraint,
FROM table1, table2, table2 WHERE table1.Column = table2.Column; 

The "equi join" with the "ON" operator syntax shows below.

SELECT Column1 data type constraint,
Column2 data type constraint,
FROM table1 JOIN table2 ON table1.Column = table2.Column; 

Examples

1) Example: the primary equijoin example shows below.

Execute the below query to understand MySQL equi join. This join requires more than one table to link with each other. Here, you can use the equal sign for the equi join operation.

mysql> SELECT d.department_name, d.department_id, 
s.subject_name, s.subject_id
FROM department AS d, subject AS s
WHERE d.department_id = s.subject_id;

OUTPUT

The above image shows department and subject table's similar information. Here, the department_id column and subject_id column have the same information. The output displays four columns of the table using equi join.

2) Example: the equi join with multiple tables' example shows below.

Execute the below query for the equi join. Here, you can use an equal sign with the WHERE clause for joining tables using equijoin operation.

mysql> SELECT d.department_name, d.department_id, 
s.subject_name, s.subject_id, m.chapter, m.level
FROM department AS d, subject AS s, mysql_tutorial AS m
WHERE d.department_id = s.subject_id = m.index;

OUTPUT

The above image shows department, subject, and mysql_tutorial table's similar information. Here, the department_id column, subject_id column, index column has the same information. The output displays six columns using equi join. The index column does not include in the output.

3) Example: the equi join with the "order by" clause example shows below.

Execute the below query for the equi join. This join requires more than one table to interconnect with each other. Here, you can use an equal sign with WHERE clauses for the equi join operation.

mysql> SELECT d.department_name, d.department_id, 
s.subject_name, s.subject_id, m.chapter, m.level
FROM department AS d, subject AS s, mysql_tutorial AS m
WHERE d.department_id = s.subject_id = m.index
ORDER BY department_id DESC;

OUTPUT

The above image shows department, subject, and mysql_tutorial table's similar information. Here, the department_id column, subject_id column, index column has the same information. The output displays six columns of the table using equi join. The index column does not include in the output. The table data shows in a descending order using the "ORDER BY" clause.

4) Example: the equi join with "group by" clause example shows below.

Execute the below query to the MySQL equi join. This join requires more than one table to link with each other.

mysql> SELECT d.department_name, d.department_id, 
s.subject_name, s.subject_id, m.chapter, m.level
FROM department AS d, subject AS s, mysql_tutorial AS m
WHERE d.department_id = s.subject_id = m.index
GROUP BY subject_id;

OUTPUT

The above image shows department, subject, and mysql_tutorial table's similar information. Here, the department_id column, subject_id column, index column has the same information. The output displays six columns of the table using equi join. This query uses the "GROUP BY" clause for grouping columns.

5) Example: the primary equi join example shows below.

Execute the below query for the equi join. This join requires more than one table to link with each other.

mysql> SELECT d.department_name, d.department_id, 
s.subject_name, s.subject_id
FROM department AS d JOIN subject AS s
ON d.department_id = s.subject_id;

OUTPUT

The above image shows department and subject table's similar information. Here, the department_id column and subject_id column have the same information. The output displays four columns of the table using equi join. The ON clause works with equi join to show the same data.

6) Example: the equi join with multiple tables' example shows below.

Execute the below query for the equi join. Here, are using equal sign with ON clauses for equi join operation.

mysql> SELECT d.department_name, d.department_id, 
s.subject_name, s.subject_id, m.chapter, m.level
FROM department AS d JOIN subject AS s JOIN mysql_tutorial AS m
ON d.department_id = s.subject_id = m.index;

OUTPUT

The above image shows department, subject, and mysql_tutorial table's similar information. Here, the department_id column, subject_id column, index column has the same information. The output displays six columns of the table using equi join. The ON clause works as an equi join to show the same data.

7) Example

Execute the below query for the equi join. Here, you can use the equal sign with the ON clause for the equi join operation.

mysql> SELECT d.department_name, d.department_id, 
s.subject_name, s.subject_id, m.chapter, m.level
FROM department AS d JOIN subject AS s JOIN mysql_tutorial AS m
ON d.department_id = s.subject_id = m.index
ORDER BY department_id DESC;

OUTPUT

The above image shows department, subject, and mysql_tutorial table's similar information. Here, the department_id column, subject_id column, index column has the same information. The output displays six columns in descending order.

8) Example:

The below query uses the equal sign with the ON clause for the equi join operation.

mysql> SELECT d.department_name, d.department_id, 
s.subject_name, s.subject_id
FROM department AS d JOIN subject AS s 
ON d.department_id = s.subject_id 
GROUP BY subject_id;

OUTPUT

The above image shows department, subject, and mysql_tutorial table's similar information. Here, the department_id column, subject_id column, index column has the same information. The output displays six columns of the table using equi join. The ON clause works as equijoin to show the same data. This query uses the "GROUP BY" clause for grouping columns.