JOINS IN SQL SERVER
Joins in SQL Server are used to
select the data from multiple tables using a single select statement.
T-SQL provides the join concept,
which allows retrieval of data from more than one table. This concept is
probably the most important for RDBMS, because it allows data to be spread over
many tables.
In SQL Server there existed three
types of joins which includes
- INNER JOIN
- OUER JOIN
- CROSS JOIN
Sample Tables
EMP
EMPNO ENAME SAL DEPTNO
----------- ---------- --------------------- -----------
11 RAGHU 10000.0000 10
22 RAZ 20000.0000 20
33 AMAR 10000.0000 10
44 MANI 15000.0000 20
55 CHARN
15000.0000 40
66 ANIL 20000.0000 50
DEPT
DEPTNO DNAME LOC
----------- ---------- ----------
10 SALES HYD
20 HR CHE
30 IR BAN
1.INNER JOIN: Inner join
selects the data from multiple tables based on the equality condition It means
it selects only matched records from the multiple tables. For doing this Inner
join operation we should have to maintain one common valued column in the
multiple tables.
Syntax:
SELECT TABLE1.COLUMN1,
TABLE1.COLUMN2,………,
TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.COMMON COLUMN=TABLE2.COMMON
COLUMN
E.g.:
SELECT EMP.EMPNO, EMP.ENAME,
DEPT.DEPTNO, DEPT.DNAME FROM EMP INNER
JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
EMPNO ENAME DEPTNO DNAME
----------- ----------
----------- ----------
11 RAGHU 10 SALES
22 RAZ 20 HR
33 AMAR 10 SALES
44 MANI 20 HR
2. OUTER JOIN: It is the
extension of Inner Join operation because Inner selects only matched records from
multiple tables where Outer join selects matched records as well as unmatched
records. It includes
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Left Outer Join: It selects matched records from both the tables as well as unmatched records from Left side table. For doing this operation we have to keep a special symbol ‘*’ at the left side of the equality condition.
Syntax:
SELECT TABLE1.COLUMN1,
TABLE1.COLUMN2,………,
TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 LEFT OUTER JOIN TABLE2
ON
TABLE1.COMMON COLUMN =TABLE2.COMMON COLUMN
E.g.:
SELECT EMP.EMPNO, EMP.ENAME,
DEPT.DEPTNO, DEPT.DNAME
FROM EMP LEFT OUTER JOIN DEPT
ON EMP.DEPTNO =DEPT.DEPTNO
EMPNO ENAME DEPTNO DNAME
----------- ---------- -----------
----------
11 RAGHU 10 SALES
22 RAZ 20 HR
33 AMAR 10 SALES
44 MANI 20 HR
55 CHARN NULL NULL
66 ANIL NULL NULL
- Right Outer Join: It selects matched records from both the tables as well as unmatched records from Right side table. For doing this operation we have to keep a special symbol ‘*’ at the right side of the equality condition.
Syntax:
SELECT TABLE1.COLUMN1,
TABLE1.COLUMN2,………,
TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 RIGHT OUTER JOIN TABLE2
ON TABLE1.COMMON COLUMN = TABLE2.COMMON COLUMN
E.g.:
SELECT EMP.EMPNO, EMP.ENAME,
DEPT.DEPTNO, DEPT.DNAME
FROM EMP RIGHT OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
EMPNO ENAME DEPTNO DNAME
----------- ----------
----------- ----------
11 RAGHU 10 SALES
22 RAZ 20 HR
33 AMAR 10 SALES
44 MANI 20 HR
NULL NULL 30 IR
- Full Outer Join: It is just combination of Left outer Join + Right outer join. It selects matched records as well as unmatched records from the given tables.
Syntax:
SELECT TABLE1.COLUMN1,
TABLE1.COLUMN2,………,
TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 FULL OUTER JOIN
TABLE2
ON TABLE1.COMMON COLUMN =TABLE2.COMMON
COLUMN
E.g.:
SELECT EMP.EMPNO, EMP.ENAME,
DEPT.DEPTNO, DEPT.DNAME
FROM EMP FULL OUTER JOIN DEPT
ON EMP.DEPTNO =DEPT.DEPTNO
EMPNO ENAME DEPTNO DNAME
----------- ----------
----------- ----------
11 RAGHU 10 SALES
22 RAZ 20 HR
33 AMAR 10 SALES
44 MANI 20 HR
55 CHARN NULL NULL
66 ANIL NULL NULL
NULL NULL 30 IR
CROSS-JOIN:
It is also known as CROSS PRODUCT or CARTESIAN
PRODUCT because it produces the product of multiple tables. Every row from
first table is multiplied with all rows of another table. Simply it is the
multiplication of two tables.
Syntax:
SELECT TABLE1.COLUMN1,
TABLE1.COLUMN2,…….
TABLE2.COLUMN1,TABLE2.COLUMN2,…….
FROM TABLE1 CROSS JOIN TABLE2
Ex:
SELECT EMP.EMPNO, EMP.ENAME,
DEPT.DEPTNO, DEPT.DNAME
FROM EMP CROSS JOIN DEPT
EMPNO ENAME DEPTNO DNAME
----------- ----------
----------- ----------
11 RAGHU 10 SALES
22 RAZ 10 SALES
33 AMAR 10 SALES
44 MANI 10 SALES
55 CHARN 10 SALES
66 ANIL 10 SALES
11 RAGHU 20 HR
22 RAZ 20
HR
33 AMAR 20 HR
44 MANI 20 HR
55 CHARN 20 HR
66 ANIL 20 HR
11 RAGHU 30 IR
22 RAZ 30 IR
33 AMAR 30 IR
44 MANI 30 IR
55 CHARN 30 IR
66 ANIL 30 IR
SELF JOIN:
Join a table with itself by
providing two table alias names is called SELF-JOIN.
Select * from DEPT, DEPT
The above statement shows an
error message because it not possible to multiply a table by itself with the
same name, so that we have to project the same table DEPT as two tables to the
SQL Server. To show a single DEPT table as two tables to server we have to use
the concept of table Alias Names.
SELECT * FROM DEPT A, DEPT B
In the above example we provided two table alias names for
the single table DEPT those are A and B. Then server identifies that there are
two tables available and it performs join operation in a normal way. According
user point of view there existed only one table but according to Server point
of view there are two tables available those are A and B.
DEPTNO DNAME
LOC DEPTNO DNAME
LOC
----------- ---------- ----------
----------- ---------- ---------- ----------------
10 SALES HYD 10
SALES HYD
20 HR CHE 10 SALES HYD
30 IR BAN 10 SALES HYD
10 SALES HYD 20 HR CHE
20 HR CHE 20 HR
CHE
30 IR BAN 20 HR CHE
10 SALES HYD 30 IR BAN
20 HR CHE 30 IR BAN
30 IR BAN 30 IR BAN
No comments:
Post a Comment