Mysql EquiJoin

Equi Join is a classified type of Inner Join in Mysql. Equi Join is used to combine records from two table based on the common column exists in both table. The Equi Join returns you only those records which are available in both table on the basis of common primary field name. The null records or unmatchable records are not to be displayed using Equi Join. The Outer Join is used to resolve these problems.

Understand with Example

The Tutorial illustrate an example to understand Equi Join in Mysql. To grasp this example, we create a table rose india with required field name and data type respectively.

Query to Create Table named roseindia:-

mysql> create table roseindia(Empid int,firstname varchar(30));
Query OK, 0 rows affected (0.05 sec)

Query to insert data into Table named roseindia:-

The insert into add the records or rows to the table rose india.

mysql>  insert into roseindia values(01,'Girish');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into roseindia values(02,'Komal');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into roseindia values(03,'Amit');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into roseindia values(04,'Sandeep');
Query OK, 1 row affected (0.03 sec)

Query to view data of Table named roseindia:-

To view the Query we use select keywords returns you the records from table roseindia.

mysql> select * from roseindia;

Output:-

+-------+-----------+
| Empid | firstname |
+-------+-----------+
|     1 | Girish    |
|     2 | Komal     |
|     3 | Amit      |
|     4 | Sandeep   |
+-------+-----------+
4 rows in set (0.00 sec)

Query to Create Table newstrack:-

Again, we use create table statement that construct a table newstrack with fieldname and data type respectively.

mysql> create table newstrack(Empid int, lastname varchar(30));
Query OK, 0 rows affected (0.03 sec)

Query to insert data into Table named newstrack:-

The insert into add the records or rows to the table newstrack.

mysql>  insert into newstrack values(01,'Suman');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into newstrack values(02,'Ravi');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into newstrack values(03,'Santosh');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into newstrack values(04,'Vinod');
Query OK, 1 row affected (0.03 sec)

Query to view data of Table named newstrack:-

mysql> select * from newstrack;

Output:-

+-------+-----------+
| Empid | firstname |
+-------+-----------+
|     1 | Suman     |
|     2 | Ravi      |
|     3 | Santosh   |
|     4 | Vinod     |
+-------+-----------+
4 rows in set (0.00 sec)

Before using inner join here we are adding one column named city in the table roseindia

Query:-

mysql> alter table roseindia add column city varchar(30);

Output:-

mysql> select * from roseindia;
+-------+-----------+----------+
| Empid | firstname | city     |
+-------+-----------+----------+
|     1 | Girish    | NULL     |
|     2 | Komal     | NULL     |
|     3 | Amit      | NULL     |
|     4 | Sandeep   | NULL     |
+-------+-----------+----------+
4 rows in set (0.00 sec)

Query:-

The Query Update modifies the table roseindia.

mysql> update roseindia set city = 'Nainital' where empid=1;
Query OK, 1 row affected (0.05 sec)

mysql> update roseindia set city = 'Merrut' where empid=1;
Query OK, 1 row affected (0.01 sec)

mysql> update roseindia set city = 'Lucknow' where empid=3;
Query OK, 1 row affected (0.00 sec)

mysql> update roseindia set city = 'Lucknow' where empid=4;
Query OK, 1 row affected (0.02 sec)

Viewing table after updating table:-

Query:-

mysql> select * from roseindia;
+-------+-----------+----------+
| Empid | firstname | city     |
+-------+-----------+----------+
|     1 | Girish    | Nainital |
|     2 | Komal     | Merrut   |
|     3 | Amit      | Lucknow  |
|     4 | Sandeep   | Lucknow  |
+-------+-----------+----------+
4 rows in set (0.00 sec)

Query to join the data of the above two tables named roseindia and newstrack using inner join:-

The below query return the selective records from roseindia and newstrack based on inner join.This is the type of join where tables are combined based on a common column.

mysql> select roseindia.firstname,roseindia.city,newstrack.firstname
    -> from roseindia,newstrack where roseindia.empid=newstrack.empid;

Output:-

+-----------+----------+-----------+
| firstname | city     | firstname |
+-----------+----------+-----------+
| Girish    | Nainital | Suman     |
| Komal     | Merrut   | Ravi      |
| Amit      | Lucknow  | Santosh   |
| Sandeep   | Lucknow  | Vinod     |
+-----------+----------+-----------+
4 rows in set (0.02 sec)

Mysql EquiJoin

Mysql EquiJoin

Mysql EquiJoin

Tutorials