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

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

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

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