1. Home
  2. MySQL EquiJoin

MySQL EquiJoin

The process is called joining when we combine two or more tables based on some common columns and a join condition. An equijoin is an operation that combines multiple tables based on equality or matching column values in the associated tables.

We can use the equal sign (=) comparison operator to refer to equality in the WHERE clause. This joining operation returns the same result when we use the JOIN keyword with the ON clause and then specifying the column names and their associated tables.

Equijoin is a classified type of inner join that returns output by performing joining operations from two tables based on the common column that exists in them. This join returns only those data that are available in both tables based on the common primary field name. It does not display the null records or unmatchable data into the result set.

Points to remember:

  • There is no need to be the same column names.
  • The resultant result can have repeated column names.
  • We can also perform an equijoin operation on more than two tables.

Syntax:

The following are the basic syntax that illustrates the equijoin operations:

OR

In this syntax, we need to specify the column names to be included in the result set after the SELECT keyword. If we want to select all columns from both tables, the * operator will be used. Next, we will specify the table names for joining after the FROM keyword, and finally, write the join condition in the WHERE and ON clause.

EquiJoin Example

Let us understand how equijoin works in MySQL through examples. Suppose we have already two tables named customer and balance that contains the following data:

MySQL EquiJoin

Execute the below equijoin statement for joining tables:

We will get the following result:

MySQL EquiJoin

We can also get the same result by using the below statement:

See the below output that is the same as the result returns from the previous query:

MySQL EquiJoin

Equi Join Using Three Tables

We know that equijoin can also perform a join operation on more than two tables. To understand this, let us create another table named cust_info using the below statement:

Then, we will fill records into this table:

We can verify the data using the SELECT statement. See the below image:

MySQL EquiJoin

To join three tables using equijoin, we need to execute the statement as follows:

It will give the below result.

MySQL EquiJoin

Difference between Natural Join, Equi Join and Inner Join

Let us summaries the differences between natural, equi and inner join operation in the tabular form given below:

Natural Join Equi Join Inner Join
It joins the tables based on the same column names and their data types. It joins the tables based on the equality or matching column values in the associated tables. It joins the tables based on the column name specified in the ON clause explicitly. It returns only those rows that exist in both tables.
It always returns unique columns in the result set. It can return all attributes of both tables along with duplicate columns that match the join condition. It returns all the attributes of both tables along with duplicate columns that match the ON clause condition.
The syntax of a natural join is given below:

SELECT [column_names | *]
FROM table_name1
NATURAL JOIN table_name2;

 The syntax of equijoin is given below:
SELECT column_name (s)

FROM table_name1, table_name2, ...., table_nameN
WHERE table_name1.column_name = table_name2.column_name;

 The syntax of inner join is given below:
SELECT [column_names | *]

FROM table_name1

INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name;
Next TopicMySQL Natural Join




Hot Tutorials

Contact US

Email:[email protected]

MySQL EquiJoin
10/30