MySQL MINUS

Created with Sketch.

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 to how to emulate the MINUS operator in MySQL using join clauses.

Introduction to SQL MINUS operator

The MINUS 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;

Code language: SQL (Structured Query Language) (sql)

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);

Code language: SQL (Structured Query Language) (sql)

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

SELECT id FROM t1
MINUS
SELECT id FROM t2;

Code language: SQL (Structured Query Language) (sql)

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 operator. 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;

Code language: SQL (Structured Query Language) (sql)

For example, the following query uses the LEFT 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;

Code language: SQL (Structured Query Language) (sql)

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

Leave a Reply

Your email address will not be published. Required fields are marked *