Join Operator in SQL
Join Operator in SQL
————————–
Left join, Right join, Inner join
Join operation in SQL is used to get results from more than one tables according to over requirement criteria.
For this used some types of join operator they are
Left Join
Right Join
Inner Join
Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
For Examples, We use three tables related to each other, They are
1. Emp Table
It contains the Personal details of the employee.
Records in the Emp Table are
+—–+——-+———-+——-+———+——-+
| eno | ename | address | city | pincode | phone |
+—–+——-+———-+——-+———+——-+
| 101 | name1 | address1 | city1 | 12345 | 12345 |
| 102 | name2 | address2 | city2 | 12345 | 23456 |
| 104 | name3 | address3 | city3 | 34567 | 34567 |
+—–+——-+———-+——-+———+——-+
here, eno (employee no.) is a Primary Key. By using this key, we integrate the other tables by using join operator.
2. Salary Table
It contains the Salary details of the employee.
Records in the Salary Table are
+—–+——–+———-+———+———+———-+
| eno | deptno | basicpay | hra | pf | netpay |
+—–+——–+———-+———+———+———-+
| 101 | 1 | 6000.00 | 5000.00 | 1000.00 | 10000.00 |
| 102 | 2 | 8000.00 | 4000.00 | 2000.00 | 10000.00 |
| 103 | 2 | 10000.00 | 5000.00 | 3000.00 | 12000.00 |
+—–+——–+———-+———+———+———-+
Left Join
———–
It is used when we need all records in the left table eventhough it not present in the
other table.
Query for Left join is
SELECT * FROM emp left join salary ON emp.eno=salary.eno
It Shows all the records in the emp table eventhough it not present in the salary table,
here eno of emp table matching with eno of salary table then, the fields in the salary table are dispayed otherwise it shows NULL. The result of the above query is
*** 1. row ***
eno: 101
ename: name1
address: address1
city: city1
pincode: 12345
phone: 12345
eno: 101
deptno: 1
basicpay: 6000.00
hra: 5000.00
pf: 1000.00
netpay: 10000.00
*** 2. row ***
eno: 102
ename: name2
address: address2
city: city2
pincode: 12345
phone: 23456
eno: 102
deptno: 2
basicpay: 8000.00
hra: 4000.00
pf: 2000.00
netpay: 10000.00
*** 3. row ***
eno: 104
ename: name3
address: address3
city: city3
pincode: 34567
phone: 34567
eno: NULL
deptno: NULL
basicpay: NULL
hra: NULL
pf: NULL
netpay: NULL
Right Join
————
It is used when we need all records in the right table even though it not present in the
other table.
Query for Right join is
SELECT * FROM emp right join salary ON emp.eno=salary.eno
It Shows all the records in the salary table even though it not present in the emp table,
here, eno of emp table matching with eno of salary table then, the fields in the emp table are dispayed otherwise it shows NULL. The result of the above query is
*** 1. row ***
eno: 101
ename: name1
address: address1
city: city1
pincode: 12345
phone: 12345
eno: 101
deptno: 1
basicpay: 6000.00
hra: 5000.00
pf: 1000.00
netpay: 10000.00
*** 2. row ***
eno: 102
ename: name2
address: address2
city: city2
pincode: 12345
phone: 23456
eno: 102
deptno: 2
basicpay: 8000.00
hra: 4000.00
pf: 2000.00
netpay: 10000.00
*** 3. row ***
eno: NULL
ename: NULL
address: NULL
city: NULL
pincode: NULL
phone: NULL
eno: 103
deptno: 2
basicpay: 10000.00
hra: 5000.00
pf: 3000.00
netpay: 12000.00
Inner Join
————-
It is used when we need all records that are present in both the tables.
Query for inner join is
SELECT * FROM emp inner join salary ON emp.eno=salary.eno
or
SELECT * FROM emp,salary WHERE emp.eno=salary.eno
It Shows the records from emp and salary table, when eno of both tables are equal.
The result of the above query is
*** 1. row ***
eno: 101
ename: name1
address: address1
city: city1
pincode: 12345
phone: 12345
eno: 101
deptno: 1
basicpay: 6000.00
hra: 5000.00
pf: 1000.00
netpay: 10000.00
*** 2. row ***
eno: 102
ename: name2
address: address2
city: city2
pincode: 12345
phone: 23456
eno: 102
deptno: 2
basicpay: 8000.00
hra: 4000.00
pf: 2000.00
netpay: 10000.00
