Oracle minus usage

Oracle minus usage

Create two tables:

Create table guotest1

(

SName varchar2 (10 ),

Gender varchar2 (10)

)

Create table guotest2

(

SName varchar2 (10 ),

Gender varchar2 (10)

)

Insert data:

Insert into guotest1 values ('A', 'M ');

Insert into guotest1 values ('B', 'M ');

Insert into guotest1 values ('C', 'F ');

Insert into guotest1 values ('D', 'F ');

Insert into guotest2 values ('E', 'M ');

Insert into guotest2 values ('F', 'M ');

Insert into guotest2 values ('G', 'F ');

Insert into guotest2 values ('h', 'F ');

1 minus returns the difference set of the two query result tables.

Example: select * from guotest1 where rownum <= 4 minus select * from guotest1 where rownum <= 2

Returned results:

"SNAME", "GENDER"

"C", "f"

"D", "f"

2. Records returned by minus always come from the table on the left.

Example: select * from guotest1 minus select * from guotest2

Returned results:

"SNAME", "GENDER"

"A", "m"

"B", "m"

"C", "f"

"D", "f"

For example, if a record in the left table exists in the right table, the record will not be returned.