SET OPERARTORS IN SQLSERVER

SET OPERARTORS

Set operators in SQL Server are used to combine the output of multiple queries.

When ever we want to combine the output of multiple queries we have to identify three factors.

1. Whether the multiple queries contains equal number of columns or not?
2. If they are equal again we have to identify whether their data types are equal or not?
3. We have to identify whether the output column name coming from first query or not?

EMP:                                                                                       DEPT:

EMPNO          ENAME          SAL    DEPTNO        DEPTNO        DNAME         LOC

11                    RAM               15000    10                    10                  SALES            HYD

22                    RAJ                 20000    20                    20                  HR                  CHE

33                    ABI                 10000    10                    30                  IR                    BAN

44                    DP                   5000      20                             

44                    NAAG                        5000      NULL                                    

In SQL Server there existed four types of Set Operators

1. UNION ALL
2. UNION
3. INTERSECT
4. EXCEPT

1. UNION ALL: It combines the output of multiple queries including duplicate values.

Syntax:  SELECT COLUMN1, COLUMN2… FROM TABLE1

                              UNION ALL

 SELECT COLUMN1, COLUMN2… FROM TABLE2

E.g.: SELECT EMPNO FROM EMP

                  UNION ALL

        SELECT DEPTNO FROM DEPT

EMPNO

            11

            22

            33

            44

            44

            10

            20

            30

2. UNION: It combines the output of multiple queries with out considering duplicate values, mean time it arranges output data in ascending order.

Syntax:  SELECT COLUMN1, COLUMN2… FROM TABLE1

                              UNION

 SELECT COLUMN1, COLUMN2… FROM TABLE2

E.g.: SELECT EMPNO FROM EMP

                  UNION

        SELECT DEPTNO FROM DEPT

EMPNO

            10

            11

            20

            22

            30

            33

            44

3. INTERSECT: It selects the common values from given set of queries.

Syntax:  SELECT COLUMN1, COLUMN2… FROM TABLE1

                              INTERSECT

 SELECT COLUMN1, COLUMN2… FROM TABLE2

E.g.: SELECT DEPTNO FROM EMP

                  INTERSECT

        SELECT DEPTNO FROM DEPT

DEPTNO

            10

            20

4. EXCEPT: It selects particular values from the first query which are not available in second query..

Syntax:  SELECT COLUMN1, COLUMN2… FROM TABLE1

                              EXCEPT

 SELECT COLUMN1, COLUMN2… FROM TABLE2

E.g.: SELECT DEPTNO FROM EMP

EXCEPT

        SELECT DEPTNO FROM DEPT

DEPTNO

        NULL