Select items that do not contain in a second table

Asked

Viewed 34 times

1

I need to select data from a table only when the codigo this table does not appear in a second table. Briefly this would be it. I have the table entregaitem with the following columns (codigo,codigoentrega,codigoestoque) and the table retorno where one of the columns is (codigoentregaitem). My need is to create a query that selects all items from the table entregaitem when codigo does not exist in the table retorno, I thought of something like:

select * from public.entregaitem where NOT EXITS 
(select public.entregaitem.codigo, public.retorno.codigoentregaitem
from public.entregaitem,public.retorno
where public.entregaitem.codigo = public.retorno.codigoentregaitem)

But so nothing was returned to me. Due to existing data where the code are different

sql select

2 answers

3


Hello, try the following query: 

select * from public.entregaitem where public.entregaitem.codigoentrega not in 
(select public.retorno.codigoentregaitem from public.retorno)

  • Perfect, it was exactly what I needed, as soon as it became available I mark your answer as correct, thank you for your help :)

  • Dear beauty, please! Good luck there.

  • Usually I don’t get good performance with not in...

  • Jefferson, a left Outer Join could also be a solution to this case?

  • Yes, it would be. Look at Caiqueromero’s answer. He gave the answer I wanted to give =]

3

There are several ways you can do this, I will demonstrate 3 here but it is important to take into account that the use of subselect or subquery may impair performance so where possible choose to avoid them.

Using NOT EXISTS together with a SUBSELECT

SELECT item.*
FROM entregaitem AS item
WHERE NOT EXISTS
   (
   SELECT NULL
   FROM retorno
   WHERE retorno.codigoentregaitem = item.codigoentregaitem
)

See example working: SQLFIDDLE

Using NOT IN together with a SUBSELECT

SELECT item.*
FROM entregaitem AS item
WHERE item.codigoentregaitem NOT IN
   (
   SELECT retorno.codigoentregaitem
   FROM retorno
   WHERE retorno.codigoentregaitem = item.codigoentregaitem
)

See example working SQLFIDDLE

Using LEFT JOIN together with the condition IS NULL

SELECT item.*
FROM entregaitem AS item
LEFT JOIN retorno 
   ON retorno.codigoentregaitem = item.codigoentregaitem
WHERE retorno.codigoentregaitem  IS NULL

See example working: SQLFIDDLE

  • Thanks for the help +1

Browser other questions tagged sql select

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