MINUS clause in Oracle, SQL Server and MySQL

Minus clause in oracle used to query the records which are exists in table1 but not in table2.

select * from table1

minus
select * from table 2

or


select column1, column2... from table1

minus
select column1, column2... from table2

Make sure the data types for the columns should be compatible.

For example  Table1 and table2 as below


Example tables
Table 1 and Table 2

select name, loc from table1
minus
select name, loca from table2

will result as below

Kishore      nyc
Vihaan       saf


Same MINUS clause in SQL Server corresponds to EXCEPT command

Here the syntax is

select * from table1
except
select * from table 2

or


select column1, column2... from table1

except
select column1, column2... from table2

However in MySQL we are not having any direct minus or except clause.


select * from table1 where (column1, column2) not in ( select * from table2);


or

select * from table1 where not exists ( select * from table2 where table2.x=table1.x and table2.y=table1.y)

No comments:

Post a Comment