What is the MINUS command for and how it works on oracle

Asked

Viewed 11,223 times

5

I’ve never seen this command before and I haven’t found it here either, I don’t know if it has another name and from the Oracle documentation I couldn’t understand exactly how it works. I just want an explanation and a simple example of the command. Thank you very much.

I would also like to know why the command below does not work ( apart is because of b.NOM_CARRO) .

SELECT b.COD_CARRO, b.NOM_CARRO FROM CARROS b
MINUS
SELECT a.COD_CARRO FROM VEICULOS a;
sql database oracle select

  • How do I return the name of the vehicle in the returned ID? Sorry if these questions are too beginner. I didn’t understand these examples, and neither did the answers.

  • but it’s wrong my code, I said it doesn’t work. If I take out the b.NOM_CARRO then it works, but I need that name.

  • Now I think I understand. You could have explained this in the question itself because it was not clear this situation..

  • I don’t know how to apply a "ignore column" using MINUS. You would have to use NOT EXISTS. I added in the answer below.

3 answers

5


The Oracle MINUS command has the meaning of "exception". It is usually used to delete data that returns in a query. Do not confuse with deleting data like DELETE. It is something quite different.

Taking your code as an example, 

SELECT b.COD_CARRO, b.NOM_CARRO FROM CARROS b
MINUS
SELECT a.COD_CARRO FROM VEICULOS a;

Suppose in table "b" there is a record whose COD_CARRO is 1.

If there is COD_CARRO = 1 in table "a", this record will be deleted from the SELECT result.

In case of selecting number of different columns, it is recommended to use the function NOT EXISTS.

In your case it would look like this:

SELECT b.COD_CARRO, b.NOM_CARRO FROM CARROS b
WHERE
NOT EXISTS
(
SELECT 0 FROM a 
WHERE 
a.COD_CARRO = b.COD_CARRO
);

  • but that code isn’t working. In the documentation asks for an argument, and if I want two so I can get the name of those who are not in the second result?

  • Whether it’s working or not, it doesn’t matter. Just understand the MINUS function.

3

A succinct form is a "minus", the result of one query minus the other.

Example : sellers who have not sold anything this month

Select codigo 
from vendedores
Minus
Select codigo_vendedor
From vendas
Where to_char(data_venda,'yyyymm') = to_char(sysdate,'yyyymm')

  • He wants to select 2 fields in the first table and compare only 1 field in the second table of "Minus". The MINUS command does not allow, so a more suitable solution would be to use NOT EXISTS.

  • Yes, select columns must have the same number and types.

1

According to the documentation Minus returns all the un-duplicated lines of the first query that is not in the second query, it is similar to 'calculating' the difference in conjunct theory. In some cases it may be equivalent to select with WHERE NOT IN().

Browser other questions tagged sql database oracle select

You are not signed in. Login or sign up in order to post.