Joins in SQL Server

JOINS

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

1.    INNER JOIN
2.    OUER JOIN
3.    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

a.    Left Outer Join
b.    Right Outer Join
c.    Full Outer Join

a.    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


b.    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


c.    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        2 0            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