Monday, 21 October 2013

What is Self Join Syntax and Example-SQL Server

SQL Server-Self Join 
Joining the table to itself is called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table.

Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

A self join could just as well be an outer join or an inner join – it just depends on how the query is written.


CREATE TABLE EMPLOYEE(EID INT,ENAME VARCHAR(15),LOCATION VARCHAR(15));
INSERT INTO EMPLOYEE VALUES(1,'RAVI','HYDERABAD'),
(2,'RANI','BANGALORE'),(3,'JOHNNY','BANGALORE'),(4,'MIKEL','HYDERABAD'),
(5,'LISA','BANGALORE'),(6,'RAVI','BANGALORE');

SELECT*FROM EMPLOYEE;

EID
ENAME
LOCATION
1
RAVI
HYDERABAD
2
RANI
BANGALORE
3
JOHNNY
BANGALORE
4
MIKEL
HYDERABAD
5
LISA
BANGALORE
6
RAVI
BANGALORE

Simple Query to display Employee details located in Hyderabad.
SELECT EID,ENAME,LOCATION FROM EMPLOYEE WHERE LOCATION='HYDERABAD';

Nested Query[Sub Query] to disply Employee details located  in Hyderabd.
SELECT EID,ENAME,LOCATION FROM EMPLOYEE WHERE LOCATION IN
(SELECT LOCATION FROM EMPLOYEE WHERE ENAME='MIKEL');

SELF JOIN to disply Employee details located in Hyderabad.
Joining the table to itself is called self join.Here in this example we join the table EMPLOYEE as E1 to its alias E2.,the E1 and E2 are the Alias names of the same table.

SELECT E1.EID,E1.ENAME,E1.LOCATION FROM EMPLOYEE E1,EMPLOYEE E2
WHERE E1.LOCATION=E2.LOCATION AND E2.ENAME='MIKEL';

Self Join Result :

EID
ENAME
LOCATION
1
RAVI
HYDERABAD
4
MIKEL
HYDERABAD

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts