How to do NOT IN SQL?

Question

How to do NOT IN SQL?

Navigation

#1 by (8 votes)
#2 by (5 votes)
#3 by (1 votes)

9

I want to get all the pilots who have never performed flights on route 12345, already experimented with NOT IN and thus:

SELECT pl.id
FROM Piloto pl,
 Voo v, Rota r           
WHERE  pl.id = v.id_comandante                  
AND pl.id = v.id_copiloto                       
AND v.cod_rota = r.cod_rota                 
AND r.cod_rota <> 12345

SQL returns nothing. What am I doing wrong?

These are the tables:

Aviao (matricula, nome, data_aquisicao, TipoAviao.cod_tipo) 

Piloto (Tripulante.id, n_aterragens, n_descolagens, n_horas_voo, tipo) 

Habilitado (Piloto.id, TipoAviao.cod_tipo, n_licenca, data_licenca) 

Rota (cod_rota, Aeroporto.cod_aeroporto_ini, Aeroporto.cod_aeroporto_fim) 

Voo (n_voo, data_partida, data_chegada, Rota.cod_rota,

Piloto.id_comandante, Piloto.id_copiloto, Aviao.matricula)

sql

asked by anonymous 25.11.2015 / 15:41

3 answers

8

I imagine the problem is that the id can not be both the master and the co-pilot at the same time, so this particular condition is if the ID is from one OR from another:

SELECT pl.id FROM Piloto pl, Voo v, Rota r
WHERE ( pl.id = v.id_comandante OR pl.id = v.id_copiloto )
   AND v.cod_rota = r.cod_rota
   AND r.cod_rota <> 12345

Your original requested to return when the id was equal to one and the other, impossible condition.

As for excluding 12345, this is probably the case with subquery:

SELECT * FROM Piloto
WHERE id NOT IN (
   SELECT pl.id FROM Piloto pl, Voo v, Rota r
   WHERE pl.id = v.id_comandante OR pl.id = v.id_copiloto
   AND v.cod_rota = r.cod_rota
   AND r.cod_rota = 12345
)

What we did here is to transfer your logic to find the id s of the pilots who made the 12345, and return in the external query the ones that are not in this role.

Anyway, the most appropriate way is to use JOIN to join the tables. Here's an explanation on the subject: What's the difference between INNER JOIN and OUTER JOIN?

25.11.2015 / 15:47

1

You need to break the queries to get the optimal result

First search All riders who have never used route X Ex.:

SELECT pl.id
FROM Piloto pl
WHERE  pl.id NOT IN (
    SELECT pl2.id
    FROM Piloto pl2, Voo v, Rota r           
    WHERE  pl2.id = v.id_comandante                  
        AND pl2.id = v.id_copiloto                       
        AND v.cod_rota = r.cod_rota                 
        AND r.cod_rota = 12345  
) z

From there you add other information.

25.11.2015 / 16:05

Creating a timer with JAVA Convert a collection of type Set to List

score 5 · Accepted Answer

@Esteves, use the IN clause with subquery very carefully because it is very expensive its processing.

Understand that for each record of each SQL relationship table, it will execute the subquery once.

So, if you have 10 pilots and have 50 routes registered, in a query you will run 500x your subquery.

For this scenario, the ideal is to use LEFT JOIN, it will be more performative and ideal.

SELECT DISTINCT pl.id
  FROM Piloto P
  LEFT JOIN Voo V ON V.ID_Comandante =  Pl.Id OR V.Id_CoPiloto = Pl.Id
  LEFT JOIN Rota R ON R.Cod_Rota = V.Cod_Rota AND R.Cod_Rota = 12345
 WHERE R.Cod_Rota IS NULL

This will return all pilot IDs that have never flown on route 12345.