How to simplify this Consultation?

Question

Have the following Question below and I would like to know if there is a way to simplify the WHERE clause

SELECT      
 idMembro,
 dataCadastro,
 nome,
 apelido,
 dataNascimento,
 telefone,
 celular,
 bairro,
 endereco,
 email,
 sexo,
 estadoCivil,
 dataBatismo,
 bloqueado,
 batizadoFora,
 usuario,
 senha
FROM membros 
WHERE
  idMembro NOT IN (SELECT idMembro FROM pastores) &&
  idMembro NOT IN (SELECT idResponsavel FROM redes) &&
  idMembro NOT IN (SELECT idResponsavel FROM regioes) &&
  idMembro NOT IN (SELECT idResponsavel FROM areas) &&
  idMembro NOT IN (SELECT idResponsavel FROM setores) &&
  idMembro NOT IN (SELECT idLider FROM celulas)
ORDER BY nome

Answer 1

This consultation is already quite simple, another way of doing it is:

SELECT *
FROM membros 
WHERE
   idMembro NOT IN (
        SELECT idMembro FROM pastores
        union SELECT idResponsavel  FROM redes
        union SELECT idResponsavel  FROM regioes
        union SELECT idResponsavel  FROM areas
        union SELECT idResponsavel  FROM setores
        union SELECT idResponsavel  FROM celulas
   ) 
ORDER BY nome

Answer 2

Another way to structure consultation (and I think the most performant):

SELECT      ME.*
FROM        membros     ME
LEFT JOIN   pastores    PA ON PA.idMembro       = ME.idMembro
LEFT JOIN   redes       RD ON RD.idResponsavel  = ME.idMembro
LEFT JOIN   regioes     RE ON RE.idResponsavel  = ME.idMembro
LEFT JOIN   areas       AR ON AR.idResponsavel  = ME.idMembro
LEFT JOIN   setores     SE ON SE.idResponsavel  = ME.idMembro
LEFT JOIN   celulas     CE ON CE.idResponsavel  = ME.idMembro
WHERE       PA.idMembro         IS NULL
        AND RD.idResponsavel    IS NULL
        AND RE.idResponsavel    IS NULL
        AND AR.idResponsavel    IS NULL
        AND SE.idResponsavel    IS NULL
        AND CE.idResponsavel    IS NULL
ORDER BY    ME.nome

If you want to go by temporary tables:

SELECT  DISTINCT X.Id
INTO    #tmpIds
FROM    (
            SELECT  idMembro        AS Id
            FROM    pastores
            UNION 
            SELECT  idResponsavel   AS Id
            FROM    redes
            UNION 
            SELECT  idResponsavel   AS Id
            FROM    regioes
            UNION 
            SELECT  idResponsavel   AS Id
            FROM    areas
            UNION 
            SELECT  idResponsavel   AS Id
            FROM    setores
            UNION 
            SELECT  idResponsavel   AS Id
            FROM    celulas
        ) X

SELECT      ME.*
FROM        membros ME
LEFT JOIN   #tmpIds TMP ON TMP.Id = ME.idMembro
WHERE       TMP.Id IS NULL
ORDER BY    ME.nome

In a way it simplifies the query leading.