MySQL: Using UNION, INTERSECT & EXCEPT

In MySQL, the UNION, INTERSECT, and EXCEPT operators are powerful tools for combining and manipulating data from multiple tables or queries. These set operators enable you to perform set operations such as combining rows, finding common elements, and subtracting sets. In this technical blog, we will explore how to use UNION, INTERSECT, and EXCEPT in MySQL with examples to illustrate their functionality.

UNION Operator

The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The SELECT statements must have the same number of columns, and the corresponding columns must have compatible data types.

The syntax for the UNION operator is as follows:

// sql

SELECT column1, column2, ... FROM table1

UNION

SELECT column1, column2, ... FROM table2;

Here’s an example that demonstrates the usage of UNION:

// sql

SELECT name, email FROM customers

UNION

SELECT name, email FROM leads;

This query combines the “name” and “email” columns from the “customers” and “leads” tables into a single result set, eliminating any duplicate rows.

INTERSECT Operator

The INTERSECT operator allows you to find the common rows between two or more SELECT statements. It returns only the rows that appear in all the SELECT statements.

MySQL does not have a built-in INTERSECT operator. However, you can achieve the same result using a combination of subqueries and JOIN operations. Here’s an example:

// sql

SELECT column1, column2, ...

FROM table1

WHERE EXISTS (

    SELECT column1, column2, ...

    FROM table2

    WHERE table1.column1 = table2.column1

      AND table1.column2 = table2.column2

)

AND EXISTS (

    SELECT column1, column2, ...

    FROM table3

    WHERE table1.column1 = table3.column1

      AND table1.column2 = table3.column2

);

In this example, we use the EXISTS clause and subqueries to find the common rows between “table1,” “table2,” and “table3” based on specific column conditions.

EXCEPT Operator

The EXCEPT operator allows you to subtract the rows from one SELECT statement that appear in another SELECT statement. It returns the rows that are unique to the first SELECT statement.

Similar to the INTERSECT operator, MySQL does not have a native EXCEPT operator. However, you can achieve the same result using a combination of subqueries and the NOT EXISTS clause. Here’s an example:

// sql

SELECT column1, column2, ...

FROM table1

WHERE NOT EXISTS (

    SELECT column1, column2, ...

    FROM table2

    WHERE table1.column1 = table2.column1

      AND table1.column2 = table2.column2

);

In this example, we use the NOT EXISTS clause and subquery to find the rows in “table1” that do not exist in “table2” based on specific column conditions.

Conclusion

The UNION, INTERSECT, and EXCEPT operators in MySQL allow you to combine, find common elements, and subtract sets of data from multiple tables or queries. The UNION operator combines the results of multiple SELECT statements, eliminating duplicates. While MySQL does not have native support for the INTERSECT and EXCEPT operators, you can achieve the same functionality using subqueries and appropriate clauses like EXISTS and NOT EXISTS. These operators provide valuable tools for manipulating and analyzing data, enabling you to perform set operations efficiently in your MySQL queries.

Let’s Discuss Your Ideas For Perfect Solutions

Integrate your ideas with our technical expertise to ensure the success of your project

Contact Us…