CodesJava

Easy learning with example program codes

MINUS in Oracle

ORACLE MINUS
To return all the rows of the first SELECT statement which are not common to the rows of the second SELECT statement, the Oracle MINUS operator is used. After the subtraction process, the MINUS operator returns the uncommon or unique records from the corresponding rows of the first SELECT statement.

There are however two mandatory conditions for using the MINUS operator in Oracle.

  • Each SELECT statement must have the same number of expressions.
  • Each corresponding expression in the different SELECT statement should be of the same data type.

Syntax:

SELECT expr_1, expr_2, ... expr_n  
FROM table1  
WHERE conditions  
MINUS  
SELECT expr_1, expr_2, ... expr_n  
FROM table2  
WHERE conditions;   

Parameters:

expr_1, expr_2, … expr_n: It is used to specify the columns of the table which needs to be retrieved.
table1, table2: It is used to specify the name of the tables from which the records need to be retrieved.
conditions: It is used to specify the conditions to be strictly followed for selection.

Example 1: Fetching single field from two tables.
Students Table:

STUDENT_ID	NAME	AGE
1	        Joy	20
2	        Smiley	19
3	        Happy	21
4	        James	22
5	        Bond	25

Teachers Table:

TEACHER_ID	NAME	AGE
101	        James	30
102	        Bond	25
103	        Smith	40

Query:

SELECT name  
FROM students
MINUS  
SELECT name 
FROM teachers;

Output:

NAME
Joy
Smiley
Happy

Explanation:
The ‘students’ and the ‘teachers’ are the already existing tables. After the subtraction, the uncommon or unique rows for the ‘name’ field from the ‘students’ table would appear.

Example 2: Fetching multiple fields from two tables.
Students Table:

STUDENT_ID	NAME	AGE
1	        Joy	20
2	        Smiley	19
3	        Happy	21
4	        James	22
5	        Bond	25

Teachers Table:

TEACHER_ID	NAME	AGE
101	        James	30
102	        Bond	25
103	        Smith	40

Query:

SELECT name, age
FROM students
MINUS  
SELECT name, age
FROM teachers;

Output:

NAME	AGE
Joy	20
Smiley	19
Happy	21

Explanation:
The ‘students’ and the ‘teachers’ are the already existing tables. After the subtraction, the uncommon or unique rows for the ‘name’ and the ‘age’ fields from the ‘students’ table would appear.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status