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:
The following are the basic syntax that illustrates the equijoin operations:
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.
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:
Execute the below equijoin statement for joining tables:
We will get the following result:
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:
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:
To join three tables using equijoin, we need to execute the statement as follows:
It will give the below result.
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: