Set Operators in SQL Server – SQL Server Tutorial

SET OPERATORS :-     

The different Set Operator are

•                 (I) UNION
•                (II) UNION ALL
•               (III) INTERSECT
•                (IV) EXCEPT

Syntax   :-

Select statement1  Union/union all/intersect/except Select statement    2

(I)   UNION Operator in SQL Server  :–

• It combines the result of two select statements.
• Union operator eliminates duplicates.
• The result is sorted.

Example  :-

Select job from emp where deptno=10 Union Select   job from emp where deptno=20

Output :-

                              Job

•                 Analyst
•                 Clerk
•                 Manager
•                 President

ROLE  :-

• To perform union operation, query 1 structure must be equal to query2 structure.
• Structure means -> number of columns & data type

                   Select job sal from emp where dno=10  Union Select job from emp where dno=20 ->error because columns are not same

Join vs Union SQL Server : –

UNION Operator in SQL Server                                            

1. Accumlates the data                        
2. {T1 U T2(▒)}                                        
3. To dissimilar structure can’t be combine with union operation.

JOIN Operator in SQL Server

1. Relates the data
2. {T1 ∞ T2(▒) (▒)}
3.  *To dissimilar structure can be joined

           Sales1                                       Sales2

Subsid salamt cusid         salesidd salesamt custid

                         Cust

   Cusid          cname     caddr

(sales1   v   sales 2) ∞ cust.

select job, sal from emp where depno=10 union Select job sal from emp where deptno=20 order by sal.

(II) union all in sql server :-

• It is similar to union.
• Here duplicates are included and result is not sorted.
• Select job from  emp where dno=10 Union all select job from emp where dno=20.

(III) intersect in sql server  :-

• It Returns common values from the result of two selection statements.
• Select job from emp where dno=10 intersect select job from emp where dno=20

(IV) except in sql server :-

• It returns values presenting in result of 1^st select statement  and not in 2^nd select statement.
• select job from emp where dno=10 except select job from emp where dno=20