Select items that do not contain a 2nd table

Navigation
1

I need to select data from a table only when the codigo of this table is not in a second table. Briefly that would be it. I have the entregaitem table with the following (codigo,codigoentrega,codigoestoque) columns and the retorno table where one of the columns is (codigoentregaitem) . My requirement is to create a query that will select all items in the entregaitem table when codigo does not exist in the retorno table, 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 nothing has been returned to me. Because there is data where the code is different

    
asked by anonymous 23.10.2017 / 15:09

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)
    
23.10.2017 / 15:15
3

There are several ways you can do this, I will demonstrate 3 here but it is important to take into account that using subselect or subquery can impair performance so whenever 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 the 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 the example working SQLFIDDLE

Using LEFT JOIN in conjunction with condition IS NULL 

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

See the example working: SQLFIDDLE

    
23.10.2017 / 15:37
How to transform str into int? Identifying SQL Brothers