EXCEPT Operator in Oracle

In Oracle SQL, the EXCEPT operator is used to retrieve distinct rows from the first query that are not present in the result of the second query. It effectively performs a set difference operation between the two queries. Here's how you can use it with an example:

Suppose you have two tables: table1 and table2, both with the same structure. You want to retrieve all distinct rows from table1 that are not present in table2.

SELECT column1, column2, ...

FROM table1

EXCEPT

SELECT column1, column2, ...

FROM table2;

In this query:

- SELECT column1, column2, ... specifies the columns you want to retrieve.

- FROM table1 specifies the first table from which to retrieve the data.

- EXCEPT is the set difference operator that retrieves distinct rows from the result of the first query that are not present in the result of the second query.

- SELECT column1, column2, ... FROM table2 specifies the second query to compare with.

Here's a concrete example:

Let's say you have two tables employees and former_employees, both with columns employee_id, first_name, and last_name. You want to retrieve all employees who are still active (present in employees table) but not former employees (present in former_employees table):

SELECT employee_id, first_name, last_name

FROM employees

EXCEPT

SELECT employee_id, first_name, last_name

FROM former_employees;

This query will return all distinct active employees who are not present in the former_employees table.

It's worth noting that in Oracle, the EXCEPT operator performs a set operation, so it automatically removes duplicate rows from the result set. If you want to include duplicates, you can use the ALL keyword like EXCEPT ALL.