MySQL MINUS

 MySQL MINUS

Summary: in this tutorial, you will learn how about SQL MINUS operator and how to simulate MINUS in MySQL using join.

Note that MySQL does not support the MINUS operator. This tutorial shows you how to emulate the MINUS operator in MySQL using join clauses.

Introduction to SQL MINUS operator

The MINUS the operator is one of three set operators in the SQL standard that includes UNION, INTERSECT, and MINUS.

The MINUS compares the results of two queries and returns distinct rows from the result set of the first query that does not appear in the result set of the second query.

The following illustrates the syntax of the MINUS operator:

SELECT select_list1 
FROM table_name1
MINUS 
SELECT select_list2 
FROM table_name2;

The basic rules for a query that uses MINUS operator are the following:

• The number and order of columns in both select_list1 and select_list2 must be the same.
• The data types of the corresponding columns in both queries must be compatible.

Suppose that we have two tables t1 and t2 with the following structure and data:

CREATE TABLE t1 (
    id INT PRIMARY KEY
);

CREATE TABLE t2 (
    id INT PRIMARY KEY
);

INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);

The following query returns distinct values from the query of the t1 the table that is not found in the result of the query of the t2 table.

SELECT id FROM t1
MINUS
SELECT id FROM t2; 

The following Venn diagram illustrates the MINUS operation:

Note that some database systems e.g., Microsoft SQL Server and PostgreSQL use the EXCEPT instead of MINUS. They have the same function.

MySQL MINUS operator emulation

Unfortunately, MySQL does not support MINUS operators. However, you can use join to emulate it.

To emulate the MINUS of two queries, you use the following syntax:

SELECT 
    select_list
FROM 
    table1
LEFT JOIN table2 
    ON join_predicate
WHERE 
    table2.column_name IS NULL; 

For example, the following query uses the LEFT JOINLEFT JOIN clause to return the same result as the MINUS operator:

SELECT 
    id
FROM
    t1
LEFT JOIN
    t2 USING (id)
WHERE
    t2.id IS NULL;

In this tutorial, you have learned about the SQL MINUS operator and how to emulate the MINUS operator in MySQL using LEFT JOIN clause.