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:

SELECT column_name (s)  

FROM table_name1, table_name2, ...., table_nameN  

WHERE table_name1.column_name = table_name2.column_name;

OR

SELECT (column_list | *)  

FROM table_name1   

JOIN table_name2   

ON table_name1.column_name = table_name2.column_name; 

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:

Execute the below equijoin statement for joining tables:

mysql> SELECT cust. customer_name, bal.balance   

FROM customer AS cust, balance AS bal  

WHERE cust.account = bal.account_num; 

We will get the following result:

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

  mysql> SELECT cust. customer_name, bal.balance   

FROM customer AS cust   

JOIN balance AS bal  

WHERE cust.account = bal.account_num;  

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:

CREATE TABLE cust_info (  

    account_no int,  

    mobile VARCHAR(15),  

    address VARCHAR(65)  

); 

Then, we will fill records into this table:

INSERT INTO cust_info (account_no, mobile, address)  

VALUES(1030, '598675498654', '777 Brockton Avenue, Abington MA 251'),   

    (2035, '698853747888', '337 Russell St, Hadley MA 103'),   

    (5564, '234456977555', '20 Soojian Dr, Leicester MA 154'),   

    (4534, '987656789666', '780 Lynnway, Lynn MA 19'),   

    (7648, '756489372222', '700 Oak Street, Brockton MA 23');  

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:

mysql> SELECT C.customer_name, C.account, B.balance, I.mobile   

FROM customer AS C, balance AS B, cust_info AS I  

WHERE C.account = B.account_num   

AND B.account_num = I.account_no;  

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:

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_name1INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name;