Quick Search:
 
 The Oracle PL/SQL MINUS Operator      [Return To Index] Jump to:  

Term: MINUS

Definition:
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.

Notes:

  1. The number of selected columns or expressions in participating queries must be same and their datatype(s) must match by position.

  2. Duplicates are automatically eliminated in the final result.

  3. The result set is sorted in ascending order by default. Explicitly, the result set can be sorted by using a positional ordering method.

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


Example Usage:

The SQL query below returns the JOB IDs from Department 10 which are not yet finalized in Department 20:

SELECT JOB_ID
FROM EMPLOYEE
WHERE DEPTNO = 10
MINUS
SELECT JOB_ID
FROM EMPLOYEE
WHERE DEPTNO = 20



Related Links:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org