SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | Except Clause

The EXCEPT clause (known as MINUS in Oracle) is a set operation in SQL that returns the difference between two result sets. It retrieves rows from the first query that are not returned by the second query. Both queries must retrieve the same number of columns, and corresponding columns must have compatible data types.

Syntax:

SELECT column1, column2, ...
FROM table1
[WHERE condition]
EXCEPT
SELECT column1, column2, ...
FROM table2
[WHERE condition];

Example:

Consider the following tables:

Table Employees:

IDName
1Alice
2Bob
3Carol

Table Managers:

IDName
2Bob
3Carol

If we want to find employees who are not managers:

SELECT ID, Name
FROM Employees
EXCEPT
SELECT ID, Name
FROM Managers;

This would return:

IDName
1Alice

Alice is the only employee who isn't a manager, so she is the only one returned in the result set.

Important Points:

  1. Order Matters: EXCEPT retrieves rows from the first query that don't have a match in the second query. Swapping the order of the queries will give different results.

  2. Distinct Results: The EXCEPT operation inherently removes duplicates. So, if the first query produces duplicates that aren't found in the second query, the result set will only contain one instance of that duplicate.

  3. Compatibility: Not all database systems support EXCEPT. For example, while SQL Server and PostgreSQL support EXCEPT, Oracle uses MINUS for the same operation. Always refer to the documentation for your specific database system.

  4. Matching Columns: Both the queries in the EXCEPT operation should have the same number of columns, and the data types of these columns should be compatible. If they aren't, you'll get an error.

In scenarios where EXCEPT or its equivalent isn't available, similar results can often be achieved using LEFT JOIN with a NULL check or using a NOT EXISTS subquery.

  1. EXCEPT in SQL:
    • Description: The EXCEPT clause is used to retrieve rows from the first query that are not present in the result of the second query.
    • Example:
      SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;

Set Operations in SQL with EXCEPT:

  1. Set Operations in SQL with EXCEPT:
    • Description: EXCEPT is part of set operations, providing a way to find the difference between two result sets.
    • Example:
      SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;

EXCEPT vs. MINUS in SQL:

  1. EXCEPT vs. MINUS in SQL:
    • Description: EXCEPT is used in SQL Server, while MINUS is used in Oracle. Both perform the same set operation.
    • Example (EXCEPT):
      SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;
    • Example (MINUS - Oracle):
      SELECT column1 FROM table1
MINUS
SELECT column1 FROM table2;

Using EXCEPT with Multiple Columns in SQL:

  1. Using EXCEPT with Multiple Columns in SQL:
    • Description: EXCEPT can be used with multiple columns to find rows with differences in all specified columns.
    • Example:
      SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

Handling NULL Values with EXCEPT in SQL:

  1. Handling NULL Values with EXCEPT in SQL:
    • Description: NULL values are treated as equal by EXCEPT, and differences in NULL values are ignored.
    • Example:
      SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;

EXCEPT Operator with Subqueries in SQL:

  1. EXCEPT Operator with Subqueries in SQL:
    • Description: EXCEPT can be used with subqueries to find differences between the results of two subqueries.
    • Example:
      (SELECT column1 FROM table1)
EXCEPT
(SELECT column1 FROM table2);

Alternatives to EXCEPT in SQL:

  1. Alternatives to EXCEPT in SQL:
    • Alternative 1 (LEFT JOIN):
      SELECT table1.column1
FROM table1
LEFT JOIN table2 ON table1.column1 = table2.column1
WHERE table2.column1 IS NULL;
    • Alternative 2 (NOT EXISTS):
      SELECT column1 FROM table1
WHERE NOT EXISTS (SELECT column1 FROM table2 WHERE table2.column1 = table1.column1);

Set Operations and Duplicates with EXCEPT:

  1. Set Operations and Duplicates with EXCEPT:
    • Description: EXCEPT removes duplicate rows from the result set.
    • Example:
      SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;

EXCEPT vs. NOT EXISTS in SQL:

  1. EXCEPT vs. NOT EXISTS in SQL:
    • Description: EXCEPT finds differences between result sets, while NOT EXISTS checks for the existence of rows.
    • Example (EXCEPT):
      SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;
    • Example (NOT EXISTS):
      SELECT column1 FROM table1
WHERE NOT EXISTS (SELECT column1 FROM table2 WHERE table2.column1 = table1.column1);

EXCEPT vs. LEFT JOIN in SQL:

  1. EXCEPT vs. LEFT JOIN in SQL:
    • Description: EXCEPT finds differences between result sets, while LEFT JOIN identifies non-matching rows.
    • Example (EXCEPT):
      SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;
    • Example (LEFT JOIN):
      SELECT table1.column1
FROM table1
LEFT JOIN table2 ON table1.column1 = table2.column1
WHERE table2.column1 IS NULL;

Combining EXCEPT with Other Operators in SQL:

  1. Combining EXCEPT with Other Operators in SQL:
    • Description: EXCEPT can be combined with other set operators like UNION, INTERSECT, and parentheses for complex queries.
    • Example:
      (SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2)
UNION
(SELECT column1 FROM table3);

Practical Use Cases for EXCEPT in SQL:

  1. Practical Use Cases for EXCEPT in SQL:
    • Example 1 (Find Missing IDs):
      SELECT employee_id FROM employees_in_department1
EXCEPT
SELECT employee_id FROM employees_in_department2;
    • Example 2 (Identify Unshipped Orders):
      SELECT order_id FROM all_orders
EXCEPT
SELECT order_id FROM shipped_orders;
Copyright © devanddep.com | All Rights Reserved