In SQL, SET operators are used to combine the results of two or more SELECT queries into a single result set. These operators are particularly useful when querying similar data from multiple tables or views.

? Main SQL SET Operators

? 1. UNION

• Removes duplicates.

• All SELECT statements must have the same number of columns and compatible data types.

SELECT City FROM CustomersUNIONSELECT City FROM Suppliers;

? Only unique cities from both tables are returned.

? 2. UNION ALL

• Keeps all duplicates.

• Slightly faster than UNION because it skips the de-duplication step.

SELECT City FROM CustomersUNION ALLSELECT City FROM Suppliers;

? All cities including duplicates are returned.

? 3. INTERSECT

• Returns only the rows that are present in both result sets.

SELECT City FROM CustomersINTERSECTSELECT City FROM Suppliers;

? Only cities found in both Customers and Suppliers are returned.

? 4. EXCEPT

• Returns rows from the first query that do not exist in the second.

SELECT City FROM CustomersEXCEPTSELECT City FROM Suppliers;

? Returns cities that are in Customers but not in Suppliers.

?? SET Operators: Rules and Requirements

1. Number of Columns: Each SELECT must return the same number of columns.

2. Data Types: The columns must have compatible data types (e.g., INT with INT, VARCHAR with VARCHAR).

3. Order By Clause: Can only be used once, at the end of the final query.

4. Aliases: Only the column names from the first query are used in the result set.

? Example with ORDER BY

SELECT FirstName FROM EmployeesUNIONSELECT FirstName FROM ManagersORDER BY FirstName;

? Use Cases

• Merging datasets from multiple sources.

• Finding common or differing data across tables.

• Auditing or comparison reports.

Would you like a visual diagram of how these operators work, or a sample database to try them out on?