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
andselect_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.