How to make a NOT IN SQL?

Question

I want to get all pilots who have never performed flights on route 12345, I have tried with NOT IN and so on:

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 I’m 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)  

Answer 1

I imagine the problem is that the id cannot be both the commander and the co-pilot, so this particular condition is if the ID is from one OR 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

Its original asked to return when the id was equal to one and the other, condition impossible.

As to delete 12345, it is probably the case of 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’ve done here is transfer your logic to locate the ids of the pilots who made the 12345, and return in the external query those who are not in that list.

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

Answer 2

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

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

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

For this scenario, the ideal is to use LEFT JOIN, will be more performatic 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 the Ids of pilots who have never flown the route 12345.

Answer 3

You need to dismember queries to achieve the optimal result

First pick up All the pilots who 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.