Select items that do not contain in a second table

Question

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

Answer 1

Hello, try the following query:

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

Answer 2

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