In Oracle PL/SQL, MINUS is one of the SQL set operators which is used to get the overhead records in the first component of a SELECT query. It returns the difference records in the upper query which are not contained by the second query.
Put another way, the MINUS operator returns only the rows remaining when the rows returned by the second query are subtracted from rows returned by the first query.
- The number of selected columns or expressions in participating queries must be same and their datatype(s) must match by position.
- Duplicates are automatically eliminated in the final result.
- The result set is sorted in ascending order by default. Explicitly, the result set can be sorted by using a positional ordering method.
- The MINUS function cannot be used with columns or expressions of LONG, BLOB, CLOB, BFILE, VARRAY, Nested table or TABLE collection expressions. The FOR UPDATE clause cannot be associated in SET operator queries.
The SQL query below returns the JOB IDs from Department 10 which are not yet finalized in Department 20:
WHERE DEPTNO = 10
WHERE DEPTNO = 20