minus mysql_A2-02-22.DML-MySQL MINUS

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

Introduction to SQL MINUS operator

MINUS is one of three set operations in the SQL standard that includes UNION, MINUS.

MINUS compares results of two queries and returns distinct rows from the first query that aren’t output by the second query.

The following illustrates the syntax of the MINUS operator:

1

2

3

SELECTcolumn_list_1FROMtable_1

MINUS

SELECTcolumns_list_2FROMtable_2;

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

The number and order of columns in both column_list_1 and column_list_2 must be the same.

The data types of the corresponding columns in both queries must be compatible.

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

1

2

3

4

5

6

7

8

9

10

CREATETABLEt1(

idINTPRIMARY KEY

);

CREATETABLEt2(

idINTPRIMARY KEY

);

INSERTINTOt1VALUES(1),(2),(3);

INSERTINTOt2VALUES(2),(3),(4);

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

1

2

3

SELECTidFROMt1

MINUS

SELECTidFROMt2;

The following Venn diagram illustrates the MINUS operator:

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

MySQL MINUS operator

Unfortunately, MySQL does not support MINUS operator. However, you can use the MySQL join to simulate it.

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

1

2

3

4

5

6

7

SELECT

column_list

FROM

table_1

LEFT JOINtable_2ONjoin_predicate

WHERE

table_2.idISNULL;

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

1

2

3

4

5

6

7

8

SELECT

id

FROM

t1

LEFT JOIN

t2USING(id)

WHERE

t2.idISNULL;

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