SQLServer: Something about "UNION", "INTERSECT" and "EXCEPT"

We all may aware of use of UNION in SQLServer but for those who are new i just want to tell them precisely that

•         Union: 

            union is to combine the results returned by two or more queries. keep this thing in  mind that its different from joins as Joins only works for joining Columns

          Example:

             Select ID, CNIC, Name  From PakistaniCitizens
              UNION
            Select ID,  SSN, Name From USCitizens

this will return results from two table having nothing in common so these can be joined.

•       Intersect & Except: 
  
  
  both of these operands returns distinct values after comparing two or more queries.
  
  
  
  • EXCEPT returns any distinct values from the left query that are not also found on the right query.
  
  
  • INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
  
  
  Example:
  
   Select ID, CNIC, Name  From PakistaniCitizens

•  lets say if it returns  500 rows
  
  now i want to grab only those Pakistani citizens who have US nationality also
  
  
  Select  Name, "Pakistan"  From PakistaniCitizens where DualNationality = 'US'
  INTERSECT
  Select  Name, PrimaryNationality From USCitizens
   
• this will return only those rows which are  common in both queries. means only those citizens which are Pakistani as well as US nationals
  
  
  Select  Name, "Pakistan"  From PakistaniCitizens where DualNationality = 'US'
  EXCEPT
  Select  Name, PrimaryNationality From USCitizens
   
• this will return only those rows which are different in both queries. means only those citizens which are either Pakistani or US nationals
  
   
  
  
  
  • Basic rules or Constraints for all three Operands 
  • The number and the order of the columns must be the same in all queries.
  • The data types must be compatible.
 

 

 I hope this article would be helpful to you.