-- UNION - eliminates duplicates:

SELECT City FROM Customers
UNION 
SELECT City FROM Suppliers
ORDER BY City;


SELECT S.CompanyName, S.City, 'Suppliers' AS TITLE
FROM Suppliers AS S
WHERE S.Country LIKE 'Brazil'
UNION
SELECT C.CompanyName,C.City, 'Customers' AS TITLE
FROM Customers AS C
WHERE C.Country LIKE 'Brazil'
ORDER BY 2

SELECT O.OrderID,C.CompanyName,O.OrderDate,'Customer' AS TITLE
FROM Customers AS C INNER JOIN Orders AS O
ON O.CustomerID=C.CustomerID
WHERE YEAR(O.OrderDate)=1996
UNION
SELECT O.OrderID,E.LastName + ' ' +
E.FirstName,O.OrderDate,'Employees'
FROM Employees AS E INNER JOIN Orders AS O
ON O.EmployeeID=E.EmployeeID
WHERE YEAR(O.OrderDate)=1996
ORDER BY O.OrderID,TITLE

-- Count orders per employee and order year inculding nulls
SELECT Employeeid, YEAR(orderdate), COUNT(*)
FROM orders
GROUP BY Employeeid, YEAR(orderdate)
UNION
SELECT Employeeid, NULL, COUNT(*)
FROM orders
GROUP BY Employeeid
UNION
SELECT NULL, NULL, COUNT(*)
FROM orders
ORDER BY 2, 1


-- UNION ALL --

SELECT City FROM Customers
UNION 
SELECT City FROM Suppliers
ORDER BY City;

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

-- EXCEPT -- 
-- Exists in the upper table but not in the bottom table
SELECT CustomerID
FROM Customers
EXCEPT
SELECT CustomerID
FROM Orders

-- INTERSECT --
-- Exists in both tables
SELECT CustomerID
FROM Customers
INTERSECT
SELECT CustomerID
FROM Orders