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 1st select statement  and not in 2nd select statement.
  • select job from emp where dno=10 except select job from emp where dno=20

Related Projects

Leave a Reply

Your email address will not be published. Required fields are marked *