If you want to get hands on with queries in this tutorial, you need to check out this page to prepare data first.

Below are the 4 tables used in this tutorial.

Table 1:

Table 2:

Table 1: with checksum column

Table 2: with checksum column

1. MINUS/EXCEPT

MINUS is an Oracle SQL set operation that selects rows from the first table and then removes rows that are also found in the second table. In Microsoft SQL Server, EXCEPT is the equivalent one to do the same thing.

The standard SQL format is like below.

Oracle:

SELECT * FROM suppliers_1_md5
MINUS
select * FROM suppliers_2_md5;

SQL Server:

SELECT * FROM suppliers_1_md5
EXCEPT
SELECT * FROM suppliers_2_md5;

Diagram below shows the query result for Table 1 MINUS/EXCEPT Table 2.

MySQL does not support MINUS/EXCEPT, the workaround is to use LEFT JOIN. Because MINUS/EXCEPT compares every column between Table 1 and Table 2, so the join clause needs to contain all 4 columns SupplierID, CompanyName, ContactName, ContactTitle. The where clause picks null values in SupplierID in Table 2, which limits to rows exist in Table 1 only.

SELECT t1.* FROM suppliers_1 AS t1
LEFT JOIN suppliers_2 AS t2 ON 
    t1.SupplierID=t2.SupplierID
    AND t1.CompanyName=t2.CompanyName
    AND t1.ContactName=t2.ContactName
    AND t1.ContactTitle=t2.ContactTitle
WHERE t2.SupplierID IS NULL;

Result: 2 rows returned.

In Table 1, there are 2 rows that don't exist in Table 2.

Query below returns the same rows as above, but the join clause only contains 1 column called Checksum. It demonstrates a practical use of function MD5 to generate a checksum value for comparison. Because MINUS/EXCEPT compares/joins all columns between Table 1 and Table 2, if the table has a hundred columns, then values in all these columns need to be compared. In real world applications, it could dramatically increase development and maintenance time and reduce query performance. The best practice is to calculate a checksum value by concatenating all columns as shown here, md5(concat(SupplierID, CompanyName, ContactName, ContactTitle)). Then only 1 column is to be joined.

SELECT t1.* FROM suppliers_1_md5 AS t1
LEFT JOIN suppliers_2_md5 AS t2 ON t1.checksum=t2.checksum
WHERE t2.SupplierID IS NULL;

Result: 2 rows returned.

2. INTERSECT

INTERSECT returns distinct rows that exist in both tables.

This is the standard format but MySQL does not support the syntax.

SELECT * FROM suppliers_1_md5
INTERSECT
SELECT * FROM suppliers_2_md5;

Diagram below shows the query result for Table 1 INTERSECT Table 2.

Below is how MySQL will do INTERSECT by using an inner join. Note that all 4 columns in table 1 are intersected with the 4 columns in table 2.

SELECT t1.* FROM suppliers_1 AS t1
JOIN suppliers_2 AS t2 ON 
    t1.SupplierID=t2.SupplierID
    AND t1.CompanyName=t2.CompanyName
    AND t1.ContactName=t2.ContactName
    AND t1.ContactTitle=t2.ContactTitle;

Result: 3 rows returned.

Here EXISTS is used and returns the same rows as above.

SELECT *
FROM suppliers_1 as t1
where EXISTS (SELECT * FROM suppliers_2 as t2
           WHERE t1.SupplierID=t2.SupplierID
        AND t1.CompanyName=t2.CompanyName
        AND t1.ContactName=t2.ContactName
        AND t1.ContactTitle=t2.ContactTitle);

You also can use sub-query with IN keyword to do INTERSECT in MySQL.

SELECT * FROM suppliers_1
WHERE (SupplierID, CompanyName, ContactName, ContactTitle)
IN (SELECT SupplierID, CompanyName, ContactName, ContactTitle FROM suppliers_2);

But we still like to use our checksum column for better performance and cleaner SQL code - only compare 1 column rather than intersect 4 columns.

SELECT t1.* FROM suppliers_1_md5 AS t1
JOIN suppliers_2_md5 AS t2 ON t1.Checksum=t2.Checksum;

Result: 3 rows returned.

EXISTS:

SELECT *
FROM suppliers_1_md5 as t1
where EXISTS (SELECT * FROM suppliers_2_md5 as t2
           WHERE t1.Checksum=t2.Checksum);

IN clause:

SELECT * FROM suppliers_1_md5
WHERE Checksum IN (SELECT Checksum FROM suppliers_2_md5);

Happy Coding!

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Another Example for Outer Joins (three tables)

4. Using Self Joins to Combine Data from the Same Table

5. SQL Set Operators - a Visual Guide to UNION, UNION ALL, MIMUS/EXCEPT, INTERSECT

6. Using UNION to Append Result Sets

7. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

8. How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows

9. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

10. How to Simulate Full Join in MySQL - Part 3: use UNION to simulate FULL JOIN

11. Use Cross Join to Combine Data in MySQL

12. Differences Between Join and Union

Back to Tutorial Index Page