OUTER JOIN com mais de duas tabelas ? Será que está mesmo “certo” ?

Boa Noite Pessoal,

A última vez que falei sobre OUTER JOIN foi um relato de um serviço de consultoria prestado para um órgão público. De fato, problemas ocorreram quando se pensa que LEFT OUTER JOIN, RIGHT OUTER JOIN, *= e =* possam significar a mesma coisa. Durante o serviço de consultoria, essa não foi a única descoberta que o órgão fez em relação aos OUTER JOINs. Normalmente o raciocínio do OUTER JOIN é uma abstração do INNER JOIN na qual a lógica é “traga todos os registros independente de ter ou não correspondentes do outro lado”. Essa visão não deixa de estar correta, mas o problema é que ela é um pouco míope e algumas particularidades enganam até mesmo os mais experientes. Será que quando há mais de duas tabelas o OUTER JOIN é realmente tão trivial assim ? Vejamos com exemplos práticos.

CREATE TABLE tblClientes (
    ClienteID INT NOT NULL IDENTITY(1,1),
    Nome VARCHAR(80) NOT NULL,
    CONSTRAINT PK_Cliente PRIMARY KEY (ClienteID))

CREATE TABLE tblCarros (
    CarroID INT NOT NULL IDENTITY(1,1),
    ClienteID INT NOT NULL,
    Placa CHAR(7) NOT NULL,
    Ano SMALLINT NOT NULL,
    Descricao VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Carro PRIMARY KEY (CarroID),
    CONSTRAINT FK_Cliente_Carro FOREIGN KEY (ClienteID)
        REFERENCES tblClientes (ClienteID))

CREATE TABLE tblObservacoes (
    ObservacaoID INT NOT NULL IDENTITY(1,1),
    CarroID INT NOT NULL,
    Observacao VARCHAR(8000) NOT NULL,
    CONSTRAINT PK_Observacao PRIMARY KEY (ObservacaoID),
    CONSTRAINT FK_Carro_Observacao FOREIGN KEY (CarroID)
        REFERENCES tblCarros (CarroID))

— Popula a tabela de Clientes
INSERT INTO tblClientes (Nome) VALUES (‘Ivone’)
INSERT INTO tblClientes (Nome) VALUES (‘Amanda’)
INSERT INTO tblClientes (Nome) VALUES (‘Mariana’)
INSERT INTO tblClientes (Nome) VALUES (‘Regiane’)
INSERT INTO tblClientes (Nome) VALUES (‘Eliane’)

— Popula a tabela de Carros
INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (1,‘JGK7044’,2006,‘Peugeot 206’)

INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (1,‘GDY7765’,2005,‘Fiat Palio’)

INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (2,‘KHZ0345’,2007,‘Vectra GT’)

INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (2,‘BDC3211’,2008,‘Fiat Stilo’)

INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (3,‘JGA7438’,2008,‘Corsa Hatch’)

— Popula a tabela de Observacoes
INSERT INTO tblObservacoes (CarroID, Observacao) VALUES (1, ‘Colocar Alarme’)
INSERT INTO tblObservacoes (CarroID, Observacao) VALUES (2, ‘Revisar motor’)
INSERT INTO tblObservacoes (CarroID, Observacao) VALUES (3, ‘Conceder tapetes de brinde’)

Além dos scripts, vale a pena fazer um breve comentário sobre o modelo de dados:

  • Todo cliente pode ou não possuir um carro
  • Todo carro pode ou não possuir uma observação (algum detalhe a ser lembrado sobre o carro)

Uma pergunta básica de SQL poderia ser formulada: “Qual seria a instrução SQL que retorne todos os clientes quer tenham carro ou não e se houver carros mostrar somente os carros que possuem alguma observação ?”. Apenas visualizando os comandos de INSERT, é visível que existem 5 clientes e apenas três carros com observação.

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Mariana

NULL

NULL

NULL

NULL

Regiane

NULL

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

NULL

O comando abaixo é a tentativa mais comum.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    LEFT OUTER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID

Vejamos então o resultado dessa consulta:

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Amanda

BDC3211

2008

Fiat Stilo

NULL

Mariana

JGA7438

2008

Corsa Hatch

NULL

Regiane

NULL

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

NULL

De fato os 5 clientes estão presentes, mas há dois carros sem observações que não deveriam ser retornados. Afinal a idéia é levantar todos os clientes e os carros com observações se houver. Ao contrário do que possa parecer o LEFT OUTER JOIN não retornou o resultado esperado. Bem, se o LEFT OUTER JOIN retorna todos os registros à esquerda independente de possuir correspondentes à direita então a construção está correta do ponto de vista de funcionamento, pois, todos os carros foram retornados independente de terem ou não correspondentes na tabela de observações. O único problema é que o resultado não foi o satisfatório para a pergunta anterior.

Os clientes devem ser retornados independente de possuirem ou não carros e o LEFT OUTER JOIN é o operador correto para isso. Os carros devem ser retornados mas somente se possuírem observações. Essa é uma aplicação correta para o INNER JOIN. Nesse caso então, bastaria substituir o segundo LEFT por um INNER. Ex:

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID

Vejamos então o resultado dessa consulta:

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Dessa vez nenhum carro sem observação foi retornado mas em contrapartida nenhum cliente sem carro apareceu na pesquisa e portanto o resultado não foi esperado. A pergunta nesse caso é: “Por que os clientes sem carro não apareceram já que foi usado o LEFT OUTER JOIN ? Não era para retornar todos os clientes quer tenham ou não carro ?”. Esse é o problema de um raciocínio voltado para apenas uma tabela. Embora ele seja verdadeiro é aplicável para apenas uma tabela e quando há mais tabelas envolvidas as coisas podem ser um pouco diferentes. A consulta abaixo irá ajudar a entender o que aconteceu.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    INNER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID

O resultado dessa consulta é idêntico ao anterior:

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Se o INNER JOIN foi utilizado, então somente os clientes que tenham carros com observações são retornados (afinal o INNER só retorna se houver a correspondência em ambas as tabelas). Uma vez que as assertivas (NULL = NULL) e (Qualquer Valor = NULL) são tidas como falsas, ao invés de interpretarmos o INNER JOIN como “obriga a ter correspondência em ambas as tabelas” podemos abstrair para “obriga a existência de registros em ambas as tabelas”. Se a obrigatoriedade de registros em ambas as tabelas é forçada pelo INNER JOIN vejamos o que acontece com a consulta LEFT e INNER.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID

O LEFT OUTER JOIN entre Clientes e Carros irá recuperar todos os clientes que tenham carros ou não. Isso produzirá uma tabela intermediária com os seguintes resultados:

Nome

Placa

Ano

Descricao

Ivone

JGK7044

2006

Peugeot 206

Ivone

GDY7765

2005

Fiat Palio

Amanda

KHZ0345

2007

Vectra GT

Amanda

BDC3211

2008

Fiat Stilo

Mariana

JGA7438

2008

Corsa Hatch

Regiane

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

Essa tabela intermediária (visível para o SQL Server) contem a relação de todos os clientes independente de ter carros e seus carros quando existem. O próximo passo é fazer a junção dessa tabela intermediária com a tabela de observações. Se for utilizado um operador LEFT entre carros e observações, o resultado dessa tabela intermediária será recuperado (independente de haver ocorrências ou não em observações) e será idêntico à primeira consulta, ou seja, retorna carros sem observação. Mas e se for utilizado um operador INNER JOIN ?

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Amanda

BDC3211

2008

Fiat Stilo

NULL

Mariana

JGA7438

2008

Corsa Hatch

NULL

Regiane

NULL

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

NULL

No caso todos os registros da tabela intermediária serão retornados, mas como o INNER JOIN obriga a existência de correspondentes em ambos os lados, todos os registros da tabela intermediária que não possuam observação serão descartados (marcados em cinza). Esse descarte irá ocorrer independente se trata-se de um cliente sem carro ou de um carro sem observação. Percebe-se então que no final das contas não importa a quantidade de operadores OUTER, se houver um operador INNER durante as junções na tabela mais abaixo, implicitamente todos os operadores OUTER são convertidos para INNER. Gastou-se recursos de processamento (os operadores OUTER normalmente são menos performáticos) e ainda não se tem o resultado esperado.

Alguém pode estar se perguntando quais seriam as alternativas. Toda vez que me deparo com alguns problemas desse tipo vejo algumas soluções criativas. A maioria tente resolver os problemas na cláusula WHERE mas quase sempre isso não retorna o resultado esperado. Das que me deparei a que funcionou é exposta abaixo:

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    INNER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID
UNION ALL
SELECT
    Nome, NULL, NULL, NULL, NULL
FROM
    tblClientes AS CLI
WHERE
    NOT EXISTS (
        SELECT * FROM tblCarros AS CAR
        WHERE CLI.ClienteID = CAR.ClienteID AND
            EXISTS (
            SELECT * FROM tblObservacoes AS OBS
            WHERE CAR.CarroID = OBS.CarroID
        )
)

A consulta consegue retornar todos os clientes quer tenham ou não carros com observações e no caso de haver carros são retornados somente os com observações. O primeiro problema dessa alternativa é que ela é muito mais dispendiosa e confusa. O segundo é que a manutenção também é prejudicada, pois não é o tipo de consulta fácil de efetuar uma manutenção no caso de alterações decorrentes de regras de negócio.

A solução para esse problema não é complexa. Durante a explicação do uso da tabela intermediária percebe-se que o LEFT foi avaliado primeiro e que o INNER foi avaliado depois, ou seja, primeiro juntou-se Clientes e Carros para depois juntar o resultado com observações. Se essa ordem puder ser controlada será possível obter o resultado desejado.

A primeira forma de se alterar a ordem em que os JOINs são processados é mudando-se a hierarquia das tabelas. Normalmente as tabelas são especificadas obedecendo uma hierarquia de relacionamentos do lado um para o lado muitos. Se um cliente pode ter muitos carros e um carro pode ter muitas observações, é uma prática de código comum especificar as tabelas nessa hierarquia durante o uso do operador FROM, ou seja, primeiro clientes, depois carros e depois observações. Embora seja comum (e inclusive recomendado) não há um nenhum impecilho para mudar essa ordem.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblCarros AS CAR
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID
    RIGHT OUTER JOIN tblClientes AS CLI ON CAR.ClienteID = CLI.ClienteID

Como a tabela de carros foi especificada primeiro é feito o INNER JOIN com a tabela de observações retornando todos os carros que tem observações. Esse resultado intermediário é combinado com um operador RIGHT OUTER JOIN e como a tabela de clientes está à direita são retornados todos os clientes (quer tenham ou não correspondentes com o resultado intermediário). Essa é uma abordagem performática, mas esteticamente é desaconselhável. Se aparecerem outras tabelas pode-se chegar em uma situação com tabelas fora da ordem hierárquica e múltiplos LEFT e RIGHT na mesma consulta favorecendo a presença de erros.

Outra forma de controlar a ordem em que esses JOINs são avaliados é a utilização de subqueries do tipo Derived Table. Essa normalmente é uma saída bem fácil e factível.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN
        (SELECT ClienteID, Placa, Ano, Descricao, Observacao FROM tblCarros AS CAR
        INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID) AS Q
ON CLI.ClienteID = Q.ClienteID

A subquery Q contém a relação dos dados de carros que tenham observações já que houve uma junção do tipo INNER JOIN. Posteriormente a tabela de Clientes é combinada com a subquery Q em uma junção do tipo LEFT OUTER JOIN. Como a tabela de clientes está à esquerda, são retornados todos os clientes quer tenham ou não correspondentes na subquery Q. Essa abordagem é interessante e menos limitada que as anteriores, mas existe uma degradação mínima de desempenho por conta da projeção dos campos para a montagem da subquery Q (o que pode ser traduzido pelos operadores do tipo Computer Scalar nos planos de execução). Uma pequena variação da consulta pode eliminar esse passo.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN
        (tblCarros AS CAR
        INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID)
            ON CLI.ClienteID = CAR.ClienteID

Essa construção (por sinal pouco conhecida) consegue retornar o resultado esperado lendo os dados diretamente sem fazer a projeção dos campos para a montagem de subqueries. Normalmente é a melhor alternativa por ser performática.

A introdução das Common Table Expressions (CTEs) no padrão ANSI99 também permite uma terceira solução para encontrar o resultado esperado conforme o script abaixo:

;WITH Q AS (SELECT ClienteID, Placa, Ano, Descricao, Observacao FROM tblCarros AS CAR
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID)

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN Q ON CLI.ClienteID = Q.ClienteID

Todas as três soluções são ANSI Compliance e funcionam em outros SGBDs, mas tanto a CTE quanto a subquery necessitam da projeção embora isso não resulta em perdas de desempenho perceptíveis.

Após demonstrar esses comportamentos, o órgão em questão questionou sobre o SQL Server e que esse comportamento era um BUG. Achar que isso é um BUG referente ao SQL Server é sim um grande equívoco. Primeiro porque a lógica está correta (ainda que alguns comportamentos sejam indesejáveis não estão errados) e segundo porque outros SGBDs tem o mesmo comportamento já que o ANSI especifica como os JOINs devem funcionar e não o SQL Server. Para tirar a prova real, fiz o teste com o PostgreSQL e o DB2 UDB e de fato as "surpresas" e soluções são as mesmas.

Na verdade o fato do duplo OUTER JOIN ser como é não representa propriamente um problema. O foco não é saber se uma construção está ou não “correta”, mas sim se ela retorna ou não o resultado esperado. Usar o LEFT OUTER JOIN com duas tabelas é algo que funciona em alguns casos mas em outros não. Usar qualquer uma das outras três construções também é algo que funciona em alguns casos e em outros não. O que realmente importa é conhecer como operadores OUTER JOIN funcionam e saber qual a construção correta para cada caso já que não há uma que funciona para todos os casos.

[ ]s,

Gustavo

5 Respostas para “OUTER JOIN com mais de duas tabelas ? Será que está mesmo “certo” ?

  1. Muito bacana, quando tinha problemas desse tipo normalmente usava uma sub-query.

  2. Legal essa explicação, bem construtiva.

  3. Explicação perfeita!Me ajudou muito.Parabéns.

  4. mUITO bom MesmoO

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s