Question

Explain set Operators with example.

Answer 1

Of course. Here is a detailed explanation of SQL SET operators with clear examples.

---

What are SET Operators?

In SQL, SET operators are used to combine the result sets of two or more SELECT statements into a single result set. They are based on the principles of mathematical set theory, allowing you to merge, find commonalities, or find differences between query results.

Think of them as tools for working with Venn diagrams, but for database tables.

Crucial Rules for Using SET Operators

Before using any SET operator, you must ensure that the SELECT statements involved are compatible. This means they must follow two strict rules:
1. Same Number of Columns: The number of columns in each SELECT statement must be the same.
2. Compatible Data Types: The data types of the corresponding columns must be compatible (e.g., you can combine a VARCHAR with a VARCHAR, or an INT with a DECIMAL, but not an INT with a DATE).

---

Sample Data for Examples

To demonstrate the operators, let's imagine we have two tables: one for employees based in the USA and one for employees based in the UK. Some employees might be contractors working for both branches.

Table 1: Employees_USA
| EmployeeID | Name |
| :--- | :--- |
| 101 | Alice |
| 102 | Bob |
| 103 | Charlie |

Table 2: Employees_UK
| EmployeeID | Name |
| :--- | :--- |
| 201 | David |
| 202 | Eve |
| 103 | Charlie |

---

The Main SET Operators

Here are the four primary SET operators used in SQL.

1. UNION

• Purpose: The UNION operator combines the result sets of two or more SELECT statements and removes any duplicate rows.

• Analogy: It returns all unique items from both sets (A ∪ B).

• Syntax:
  `sql
  SELECT column1, column2, ... FROM table1
  UNION
  SELECT column1, column2, ... FROM table2;
  `

• Example:
  To get a single, consolidated list of all unique employee names from both the USA and UK offices.

  `sql
  SELECT Name FROM Employees_USA
  UNION
  SELECT Name FROM Employees_UK;
  `

• Result:
  The list will contain every name, but "Charlie," who is in both tables, will only appear once.
  | Name |
  | :--- |
  | Alice |
  | Bob |
  | Charlie |
  | David |
  | Eve |

2. UNION ALL

• Purpose: The UNION ALL operator combines the result sets of two or more SELECT statements and includes all rows, including duplicates.

• Analogy: It simply appends one list to the end of the other. It is faster than UNION because it doesn't need to check for and remove duplicates.

• Syntax:
  `sql
  SELECT column1, column2, ... FROM table1
  UNION ALL
  SELECT column1, column2, ... FROM table2;
  `

• Example:
  To get a raw list of all employee entries from both offices.

  `sql
  SELECT Name FROM Employees_USA
  UNION ALL
  SELECT Name FROM Employees_UK;
  `

• Result:
  "Charlie" will appear twice in this list because UNION ALL does not remove duplicates.
  | Name |
  | :--- |
  | Alice |
  | Bob |
  | Charlie |
  | David |
  | Eve |
  | Charlie |

3. INTERSECT

• Purpose: The INTERSECT operator returns only the rows that exist in both result sets.

• Analogy: It returns the overlapping section of a Venn diagram (A ∩ B).

• Syntax:
  `sql
  SELECT column1, column2, ... FROM table1
  INTERSECT
  SELECT column1, column2, ... FROM table2;
  `

• Example:
  To find the employees who work for both the USA and UK branches.

  `sql
  SELECT Name FROM Employees_USA
  INTERSECT
  SELECT Name FROM Employees_UK;
  `

• Result:
  Only "Charlie" is present in both tables, so only that name is returned.
  | Name |
  | :--- |
  | Charlie |

4. EXCEPT (or MINUS in some databases like Oracle)

• Purpose: The EXCEPT operator returns all the distinct rows from the first SELECT statement that are not found in the second SELECT statement. The order of the queries matters.

• Analogy: It returns the part of the first set that does not overlap with the second set (A - B).

• Syntax:
  `sql
  -- In SQL Server, PostgreSQL, etc.
  SELECT column1, column2, ... FROM table1
  EXCEPT
  SELECT column1, column2, ... FROM table2;

  -- In Oracle
  SELECT column1, column2, ... FROM table1
  MINUS
  SELECT column1, column2, ... FROM table2;
  `

• Example 1: Find employees who are only in the USA office.

  `sql
  SELECT Name FROM Employees_USA
  EXCEPT
  SELECT Name FROM Employees_UK;
  `

• Result 1:
  This returns "Alice" and "Bob" because they are in Employees_USA but not in Employees_UK.
  | Name |
  | :--- |
  | Alice |
  | Bob |

• Example 2: Find employees who are only in the UK office.
  Notice we just swap the order of the tables.

  `sql
  SELECT Name FROM Employees_UK
  EXCEPT
  SELECT Name FROM Employees_USA;
  `

• Result 2:
  This returns "David" and "Eve" because they are in Employees_UK but not in Employees_USA.
  | Name |
  | :--- |
  | David |
  | Eve |