NOT IN

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 13 Nov 2007 11:50 am

Guys this afternoon had a DBA here in the company and had a select that not returning the expected result, hence he used this thing, NOT IN rs could someone so practice tell me what it's for?

rs
Code: Select all
SELECT *
FROM TITULO_A_RECEBER TT WHERE tt.nrultimo_envio = '77'
AND TT.DTVENCIMENTO = '30/11/2007'
AND TT.CDCLIENTE NOT IN (select tt.cdcliente
from titulo_a_receber tt, pessoa p, usuario u, endereco e, cidade c
where
tt.nrregistro_cliente = p.nrregistro and
tt.nrregistro_cliente = u.nrregistro_usuario and
tt.nrregistro_cliente = e.nrregistro and
tt.dtvencimento = '30/11/2007'and)
carlos12
Location: rio

Poston Tue, 13 Nov 2007 12:01 pm

Brother, NOT IN: means that is not between (or, in the list).

For example, you have a products table, which has the product code and product group.

You have to perform a select and return all records, EXCEPT the Group's 99 and 100.

Then you can use the NOT IN to distinguish this group that you do not want to display the items.

Example:
Code: Select all
         SELECT *
     FROM  TB_PRODUTOS
    WHERE CD_GRUPO_PROD NOT IN (99,100);
   
Or, posing a table of values (as your example above):
Code: Select all
    SELECT *
      FROM TB_PRODUTOS
     WHERE CD_GRUPO_PROD NOT IN (SELECT CD_GRUPO                                   FROM TAB_GRUPO_PRODUTOS);
            
            
important note: for reasons of performance, sometimes is necessary the use of the functions \"EXISTS\" or \"NOT EXISTS\", instead of NOT IN, but, all of this depends on the cost of your query.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Tue, 13 Nov 2007 3:53 pm

Man brigadao for the clarification valews of truth
carlos12
Location: rio

Poston Tue, 13 Nov 2007 4:11 pm

Imagin brother, needing, we're there.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP



Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests