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]

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…