13-SQL-Set_Operator

SET Operators in SQL

Set operators are used to combine results from two or more SELECT queries.

The two most common ones are:

1️⃣ UNION
UNION combines results and removes duplicates.

2️⃣ UNION ALL
UNION ALL combines results and keeps duplicates.

🔹UNION and UNION ALL
 Union aand Union ALL will combine the results from two queries, but the difference is 'UNION' will not allow duplicates but 'UNION ALL' will allow duplicates.

 Example:

-- find the customers from chennai and delhi
 SELECT * FROM cust_dimen
WHERE city='Chennai'
UNION ALL
SELECT * FROM cust_dimen
WHERE city='Delhi';

-- Show all records from both tables, including matched and unmatched rows, by combining LEFT JOIN and RIGHT JOIN results.
cust_dimen cd LEFT JOIN market_fact_full mff
ON cd.cust_id=mff.cust_id
UNION 
SELECT * FROM 
cust_dimen cd RIGHT JOIN market_fact_full mff
ON cd.cust_id=mff.cust_id;

-- find customers from Nagpur and Mumbai
SELECT cust_id, city, customer_segment
FROM cust_dimen
WHERE city='Nagpur'
UNION ALL
SELECT cust_id, city, customer_name, customer_segment
FROM cust_dimen
WHERE city='Mumbai';