All of the supported data_types can be NULL. Although NULL is not a value, but the absence of a value, it is sometimes referred to as a “NULL value”.

Use this information when declaring column types with the CREATE TABLE statement or casting values to new data_types for use in functions or output.

Additional Information

  • Nulls can be referenced directly in some commands, such as comparators.

Compares two queries and returns distinct rows only based on the keyword provided:

  • EXCEPT: only the rows from the first query that are not included in results for the second query.

  • INTERSECT: only the rows that are included in results for both queries.

Syntax


query1 { EXCEPT | INTERSECT } query2 ;

Arguments

  • query1, query2: Any valid SQL query. The number of columns in query1 and query2 must be the same.

  • The data_types for each of the matching columns across the queries must either be the same or implicitly convertible between the data_types.

Additional Information

  • When comparing results, NULL values are considered to match other NULL values.

  • Column names used in the final result set are the column names from query1.

Examples


-- Returns a list of employees with gross pay over 10000 in both
-- 2016 and 2017
SELECT empID, firstName, lastName FROM MYSQL.hr2016.payroll WHERE grossPay > 10000
INTERSECT
SELECT empID, firstName, lastName FROM MYSQL.hr2017.payroll WHERE grossPay > 10000;

-- Returns a list of employees with gross pay over 10000 in 2016 but
-- NOT in 2017
SELECT empID, firstName, lastName FROM MYSQL.hr2016.payroll WHERE grossPay > 10000
EXCEPT
SELECT empID, firstName, lastName FROM MYSQL.hr2017.payroll WHERE grossPay > 10000;

-- Returns a list of employees with gross pay over 10000 in 2017 but
-- NOT in 2016
SELECT empID, firstName, lastName FROM MYSQL.hr2017.payroll WHERE grossPay > 10000
EXCEPT
SELECT empID, firstName, lastName FROM MYSQL.hr2016.payroll WHERE grossPay > 10000;
IS [ NOT ] NULL