SQL Standard Set Operations: MS SQL Server vs MySQL

SQL Standard Set Operations: MS SQL Server vs MySQL.md

1. SQL Standard Set Operations: MS SQL Server vs MySQL, by GPT

SQL supports set operations to combine results from multiple SELECT queries. Here’s a comparison of MS SQL Server and MySQL regarding these operations.

---

1. MS SQL Server

Supported set operations:

• UNION
• UNION ALL
• INTERSECT
• EXCEPT

Syntax examples:

-- UNION: combines results, removes duplicates
SELECT column1 FROM tableA
UNION
SELECT column1 FROM tableB;

-- UNION ALL: combines results, keeps duplicates
SELECT column1 FROM tableA
UNION ALL
SELECT column1 FROM tableB;

-- INTERSECT: returns only rows present in both queries
SELECT column1 FROM tableA
INTERSECT
SELECT column1 FROM tableB;

-- EXCEPT: returns rows from the first query not in the second
SELECT column1 FROM tableA
EXCEPT
SELECT column1 FROM tableB;

---

2. MySQL

Supported set operations:

• UNION
• UNION ALL

Not supported (as of MySQL 8.0):

• INTERSECT
• EXCEPT (or MINUS)

Syntax examples:

-- UNION: combines results, removes duplicates
SELECT column1 FROM tableA
UNION
SELECT column1 FROM tableB;

-- UNION ALL: combines results, keeps duplicates
SELECT column1 FROM tableA
UNION ALL
SELECT column1 FROM tableB;

To simulate INTERSECT and EXCEPT in MySQL, use JOINs or subqueries:

-- INTERSECT simulation
SELECT column1 FROM tableA
WHERE column1 IN (SELECT column1 FROM tableB);

-- EXCEPT simulation
SELECT column1 FROM tableA
WHERE column1 NOT IN (SELECT column1 FROM tableB);

---

Summary Table

Operation	MS SQL Server	MySQL (8.0+)	Notes/Workarounds
UNION	Yes	Yes	Removes duplicates
UNION ALL	Yes	Yes	Keeps duplicates
INTERSECT	Yes	No	Use IN/JOIN workaround
EXCEPT	Yes	No	Use NOT IN/LEFT JOIN

---

---

---

2. SQL Standard Set Operations: MS SQL Server vs MySQL, by Gemini

The standard set operations, complete with definitions, examples, analogies, and visual descriptions using Venn diagrams.

First, What is a Set?

Before we get to the operations, let's define a set. A set is a collection of distinct, unordered items.

• Distinct: Each item in a set is unique. You can't have the same item twice. For example, {1, 2, 2, 3} is not a proper set; it would just be {1, 2, 3}.
• Unordered: The order of items doesn't matter. {1, 2, 3} is the exact same set as {3, 1, 2}.

For all the examples below, let's use these two sets:

• A = {1, 2, 3, 4}
• B = {3, 4, 5, 6}

---

1. Union ( ∪ )

The union of two sets is a new set containing all the elements that are in at least one of the two sets. It essentially merges the two sets together, removing any duplicates.

• Keyword: OR

• Question: "What elements are in set A OR set B (or both)?"

• Analogy: If you have two playlists of songs, the union is a single new playlist with all the songs from both, but with no duplicate songs.

• Venn Diagram: The union is represented by the entire area covered by both circles.

• Example: A ∪ B = {1, 2, 3, 4, 5, 6} (Notice that 3 and 4, which are in both sets, are only listed once)

---

2. Intersection ( ∩ )

The intersection of two sets is a new set containing only the elements that are in both sets. It's the common ground between the sets.

• Keyword: AND

• Question: "What elements are in set A AND set B?"

• Analogy: If you and a friend compare your book collections, the intersection is the list of books that you both own.

• Venn Diagram: The intersection is represented by the overlapping area of the two circles.

• Example: A ∩ B = {3, 4} (Only 3 and 4 are present in both A and B)

---

3. Difference ( - or \ )

The difference between two sets (e.g., A - B) is a new set containing elements that are in the first set but not in the second set. The order matters here!

• Keyword: NOT

• Question: "What elements are in set A BUT NOT in set B?"

• Analogy: Imagine a list of all guests invited to a party (Set A) and a list of guests who have confirmed they can't come (Set B). The difference (A - B) is the final list of guests who might still attend.

• Venn Diagram: The difference A - B is the area of circle A that does not overlap with circle B.

• Example 1: A - B = {1, 2} (These are the elements in A that are not in B)

• Example 2 (order reversed): B - A = {5, 6} (These are the elements in B that are not in A)

---

4. Complement ( ' or ᶜ )

The complement of a set is everything that is not in that set. To define the complement, you first need a Universal Set (U), which is the set of all possible elements for the context you're working in.

• Keyword: NOT
• Question: "What elements in the universe are NOT in set A?"
• Analogy: If the Universal Set is all the students in a school, the complement of the "Chess Club" set is all the students in the school who are not in the Chess Club.
• Venn Diagram: The complement is everything outside the circle for that set, but still inside the rectangle representing the Universal Set.

Let's define a Universal Set for our example: U = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}

• Example: The complement of A (written as A' or Aᶜ) is: A' = {5, 6, 7, 8, 9, 10}

---

5. Symmetric Difference ( Δ or ⊕ )

The symmetric difference of two sets is a new set containing elements that are in either of the sets, but not in both. It's the "exclusive OR".

• Keyword: EITHER/OR, BUT NOT BOTH

• Question: "What elements are in A or in B, but not in their intersection?"

• Analogy: If some people like only coffee (Set A) and others like only tea (Set B), the symmetric difference is the group of people who like either coffee or tea, but not both.

• Venn Diagram: It is represented by the areas of both circles except for their overlapping part.

• Example: A Δ B = {1, 2, 5, 6} (You can also think of this as (A ∪ B) - (A ∩ B))

Summary Table

Operation	Symbol	Definition	Example with A={1,2,3,4}, B={3,4,5,6}
Union	A ∪ B	All elements in A, or B, or both.	{1, 2, 3, 4, 5, 6}
Intersection	A ∩ B	All elements in both A and B.	{3, 4}
Difference	A - B	All elements in A but not in B.	{1, 2}
Complement	A'	All elements in the Universal Set (U) but not in A.	{5, 6, 7, 8, 9, 10} (using U)
Symmetric Difference	A Δ B	All elements in A or B, but not both.	{1, 2, 5, 6}

---

---

SQL Standard Set Operations: MS SQL Server vs. MySQL

Excellent follow-up. Applying set theory to SQL is a perfect way to understand its practical power. SQL provides operators that directly correspond to the standard set operations, but they work on the results of SELECT queries, not on the tables themselves.

Here’s a breakdown of the standard set operators in both MS SQL Server (using its T-SQL dialect) and MySQL.

Core Rules for All SQL Set Operators

Before we start, there are two critical rules that apply to any query using these operators:

1. Same Number of Columns: Each SELECT statement in the operation must have the same number of columns.
2. Compatible Data Types: The data types of the columns in corresponding positions must be compatible (e.g., you can combine a VARCHAR(10) with a VARCHAR(20), but you can't easily combine a DATETIME with an INT).

---

Sample Data Setup

Let's use two simple tables for our examples: Employees and Consultants.

MS SQL Server (T-SQL) Syntax

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

CREATE TABLE Consultants (
    ID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Populate data
INSERT INTO Employees VALUES (1, 'Alice', 'Williams'), (2, 'Bob', 'Johnson'), (3, 'Charlie', 'Brown');
INSERT INTO Consultants VALUES (101, 'Charlie', 'Brown'), (102, 'David', 'Smith'), (103, 'Eve', 'Davis');

MySQL Syntax

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

CREATE TABLE Consultants (
    ID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Populate data
INSERT INTO Employees VALUES (1, 'Alice', 'Williams'), (2, 'Bob', 'Johnson'), (3, 'Charlie', 'Brown');
INSERT INTO Consultants VALUES (101, 'Charlie', 'Brown'), (102, 'David', 'Smith'), (103, 'Eve', 'Davis');

---

1. UNION - The Union Operation (∪)

Combines the result sets of two or more SELECT statements and removes duplicate rows.

MS SQL Server & MySQL

The syntax and behavior are identical in both systems.

-- Get a list of all unique people across both tables
SELECT FirstName, LastName FROM Employees
UNION
SELECT FirstName, LastName FROM Consultants;

Result: (The order of rows is not guaranteed without an ORDER BY clause)

FirstName	LastName
Alice	Williams
Bob	Johnson
Charlie	Brown
David	Smith
Eve	Davis

Notice that 'Charlie Brown', who exists in both tables, appears only once.

---

2. UNION ALL - A Variation of Union

Combines the result sets of two or more SELECT statements but does not remove duplicate rows. It is much faster than UNION because it doesn't need to check for duplicates.

MS SQL Server & MySQL

The syntax and behavior are identical.

-- Get a list of all people, including duplicates
SELECT FirstName, LastName FROM Employees
UNION ALL
SELECT FirstName, LastName FROM Consultants;

Result:

FirstName	LastName
Alice	Williams
Bob	Johnson
Charlie	Brown
Charlie	Brown
David	Smith
Eve	Davis

---

3. INTERSECT - The Intersection Operation (∩)

Returns only the rows that appear in both result sets.

MS SQL Server

MS SQL Server fully supports INTERSECT.

-- Get people who are both an Employee and a Consultant
SELECT FirstName, LastName FROM Employees
INTERSECT
SELECT FirstName, LastName FROM Consultants;

Result:

FirstName	LastName
Charlie	Brown

MySQL

• MySQL 8.0 and later: INTERSECT is natively supported and works exactly like in MS SQL Server.

  -- (This works in MySQL 8.0+)
  SELECT FirstName, LastName FROM Employees
  INTERSECT
  SELECT FirstName, LastName FROM Consultants;

• MySQL (older versions): INTERSECT is not supported. You must emulate it, typically with an INNER JOIN or IN.

  Emulation with INNER JOIN:

  SELECT DISTINCT e.FirstName, e.LastName
  FROM Employees e
  INNER JOIN Consultants c ON e.FirstName = c.FirstName AND e.LastName = c.LastName;

  This query joins the two tables on the condition that the names match, effectively finding the intersection.

---

4. EXCEPT - The Difference Operation (-)

Returns the distinct rows from the first (left) query that are not found in the second (right) query. The order matters!

MS SQL Server

MS SQL Server uses the EXCEPT keyword.

-- Example 1: Employees who are NOT Consultants
SELECT FirstName, LastName FROM Employees
EXCEPT
SELECT FirstName, LastName FROM Consultants;

Result:

FirstName	LastName
Alice	Williams
Bob	Johnson

-- Example 2: Consultants who are NOT Employees (order reversed)
SELECT FirstName, LastName FROM Consultants
EXCEPT
SELECT FirstName, LastName FROM Employees;

Result:

FirstName	LastName
David	Smith
Eve	Davis

MySQL

• MySQL 8.0 and later: EXCEPT is natively supported and works exactly like in MS SQL Server.

  -- (This works in MySQL 8.0+)
  SELECT FirstName, LastName FROM Employees
  EXCEPT
  SELECT FirstName, LastName FROM Consultants;

• MySQL (older versions): EXCEPT is not supported. The standard way to emulate it is with a LEFT JOIN ... WHERE ... IS NULL.

  Emulation with LEFT JOIN:

  -- Emulating: Employees EXCEPT Consultants
  SELECT e.FirstName, e.LastName
  FROM Employees e
  LEFT JOIN Consultants c ON e.FirstName = c.FirstName AND e.LastName = c.LastName
  WHERE c.ID IS NULL; -- The key part: keep only rows from Employees that found no match in Consultants

  This query returns the same result as EXCEPT by finding all Employees who do not have a matching Consultant.

---

Summary Table: MS SQL Server vs. MySQL

Operation	Purpose	MS SQL Server Support	MySQL Support	Key Notes
UNION	Combines results, removes duplicates.	✅ Yes	✅ Yes	Behavior is identical.
UNION ALL	Combines results, keeps duplicates.	✅ Yes	✅ Yes	Behavior is identical. Faster than UNION.
INTERSECT	Returns rows common to both queries.	✅ Yes	✅ Yes (8.0+) ❌ No (older)	Older MySQL versions require emulation with INNER JOIN or IN.
EXCEPT	Returns rows in the first query but not the second.	✅ Yes	✅ Yes (8.0+) ❌ No (older)	Older MySQL versions require emulation with LEFT JOIN...WHERE IS NULL.