SQL Server – SET Operators Examples

-------------------------------
-- SET Operators
-------------------------------

CREATE TABLE zugi
(id int)

CREATE TABLE ee_zugi
(id int) 

INSERT INTO zugi VALUES (1) , (2) , (4) , (6) , (8) , (10) 

INSERT INTO ee_zugi VALUES (1) , (3) , (5) , (7) , (9) , (10)

SELECT * FROM zugi 

SELECT * FROM ee_zugi 

SELECT id  FROM zugi
UNION ALL
SELECT id FROM ee_zugi 

SELECT id  FROM zugi
UNION
SELECT id FROM ee_zugi 

SELECT id  FROM zugi
INTERSECT
SELECT id FROM ee_zugi

SELECT id  FROM zugi
EXCEPT
SELECT id FROM ee_zugi  

SELECT id , 'ee_zugi' AS 'mashatarotse' FROM ee_zugi
UNION ALL
SELECT id , 'zugi' FROM zugi  

SELECT city FROM customers
UNION
SELECT ShipCity FROM orders 

SELECT productID , productName , unitPrice FROM products WHERE unitPrice > 50
EXCEPT
SELECT productID , productName , unitPrice FROM products WHERE productName LIKE '%a%' 

SELECT productID , productName , unitPrice FROM products WHERE unitPrice > 50
INTERSECT
SELECT productID , productName , unitPrice FROM products WHERE productName LIKE '%a%' 

SELECT ShipCity , 'Orders' FROM Northwind.dbo.orders
UNION ALL
SELECT cityname , 'Cities' FROM [AdventureWorks2012].[dbo].[city]