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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
