Será que LEFT OUTER JOIN, RIGHT OUTER JOIN, *= e =* são sinônimos ?

Olá Pessoal,

Estive no aeroporto de Atlanta e fiquei esperando 4 horas para o meu vôo de volta para o Brasil. Enquanto esperava nada melhor do que planejar um artigo. No ano de 2008, participei de uma consultoria em um órgão público para a migração de um base de dados em SQL Server 2000 para o SQL Server 2005. A idéia era fazer apenas o projeto de migração enquanto o órgão ficaria responsável por implementá-lo (sobre a minha supervisão). Alguns podem até pensar que uma migração do SQL Server 2000 para o 2005 é tão simples quanto migrar os documentos do Excel 2003 para o Excel 2007, ou seja, talvez um simples backup ou um attach sejam suficientes. De fato isso é verdade em muitas situações, mas nem sempre é assim.

Ao chegar no órgão, me deparei com diversos pontos a serem resolvidos, ou seja, faltavam ainda as máquinas que hospedariam o 2005, a definição de que bases deveriam ir primeiro, quem faria as correções necessárias, etc. Em um universo de pouco mais de 100 SPs, o Upgrade Advisor capturou 19 SPs para correção. Na maioria das SPs o problema era o mesmo, ou seja, a presença dos operadores *= e =* que não são mais permitidos no SQL Server 2005. A solução parecia ser bastante simples, ou seja, bastaria substituir o *= e o =* respectivamente por LEFT OUTER JOIN e RIGHT OUTER JOIN. Sim, isso é o que pensam a maioria dos desenvolvedores, DBAs, consultores e muitos outros profissionais (e talvez seja inclusive o que você julgue ser verdadeiro). A verdade é que isso é correto na maioria das situações, mas não em todas e julgar que substituir o *= e o =* por LEFT OUTER JOIN e RIGHT OUTER JOIN sempre é o caminho correto pode levá-lo a interpretações equivocadas e erros em algumas consultas.

O objetivo desse artigo não é propriamente falar sobre a migração, mas sobre esse ponto especificamente. Não subestime o artigo apenas pelo título, pois, você pode se surpreender. Talvez algumas construções tidas como inquestionáveis devam ser revistas.

Um pouco sobre o ANSI e as extensões proprietárias

Em muitas discussões veja uma certa confusão sobre o uso dos JOINs. Alguns dizem que juntar os campos via JOIN é o padrão ANSI e juntar os campos via cláusula WHERE não é o padrão ANSI. Sim, há razões para que se pense assim, mas a verdade é que as duas construções obedecem ao padrão ANSI (só que como veremos os JOINS obedecem a uma especificação mais recente). Voltemos alguns anos atrás para entender as proposições do ANSI.

A linguagem SQL nem sempre foi como ela é, ou seja, universal e adaptável a quase todos os bancos de dados relacionais e objeto-relacionais da atualidade. Ela não era uma linguagem padrão e cada banco de dados utilizava uma linguagem proprietária para consulta. Em meados de 1986, o comitê ANSI reuniu-se e definiu como a SQL deveria comportar-se e a partir daí foi feita uma gradativa adoção por parte dos fabricantes de bancos de dados. Como todo padrão, a medida que o tempo passa, identificam-se novas necessidades que não foram pensadas antes e por conseqüência não contempladas no padrão. Por isso, de tempos em tempos, o padrão tem de ser revisto (isso ocorre com a SQL, com os padrões XML e com muitos outros na área de TI). Em 1989 foi feita uma nova revisão para incorporar novas necessidades e o padrão continuou a ganhar força (essa revisão ficou conhecida como SQL89). Havia uma particularidade nesse padrão. Ele não possuia JOINs. Como então juntar as informações de tabelas diferentes ? Essa é a razão pela qual as junções eram feitas via cláusula WHERE. Ex:

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes, tblCarros
WHERE tblClientes.ClienteID = tblCarros.ClienteID

Isso explica porque há tantas junções na cláusula WHERE. Os analistas mais antigos usavam esse tipo de construção e foram repassando-a para os analistas mais experientes. Embora o SQL89 permitesse a junção via cláusula WHERE (desconfio que isso já era possível desde a padronização de 1986), alguns problemas não demoraram a aparecer. A consulta acima retorna todos os clientes e os dados de seus carros, mas como é feita uma junção na cláusula WHERE, obrigatoriamente são retornados apenas os clientes que tem carros. Como retornar então a mesma consulta, mas incluir os clientes que não possuem carros ? Essa era uma necessidade que o padrão SQL89 não conseguia resolver, pois, senão havia JOINs não poderíamos utilizar o LEFT OUTER JOIN.

Visualizando essas limitações, alguns fabricantes (a exemplo da ORACLE e da Microsoft) introduziram novas construções para estender o padrão SQL89. Como cada fabricante faz suas próprias extensões, é por essa razão que são conhecidas como extensões proprietárias. Se todos os fabricantes concordassem em suas extensões, elas não seria proprietárias e provavelmente fariam parte de alguma revisão posterior. Seguem algumas simulações SQL89 para simular o LEFT OUTER JOIN.

Microsoft

Oracle

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes, tblCarros
WHERE tblClientes.ClienteID *= tblCarros.ClienteID

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes, tblCarros
WHERE tblClientes.ClienteID = tblCarros.ClienteID(+)

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes, tblCarros
WHERE tblCarros.ClienteID (+)= tblClientes.ClienteID

Com construções desse tipo, foi possível que a Microsoft e a Oracle pudessem estender o padrão SQL89 e atender situações do tipo LEFT OUTER JOIN ou RIGHT OUTER JOIN. É justamente por esse tipo de implementação, que é bem comum ver junções na cláusula WHERE. Afinal com as extensões proprietárias, era possível aos analistas utilizá-las e atender às necessidades comuns e assim repassá-las aos analistas mais novos.

Em 1992, houve uma nova reunião no comitê para rever o padrão SQL. Essa revisão foi uma das mais importantes e talvez seja a implementação mais obedecida pelos bancos de dados atuais (mas ainda assim nenhum SGBD a adotou completamente). Essa revisão definiu vários pontos importantes (em especial os JOINs) e ficou conhecida como ANSI92, SQL92 ou ainda SQL2. Vale a pena lembrar que um revisão não "nega" a revisão anterior. Boa parte do padrão SQL89 está presente no SQL92 e assim sucessivamente. O SQL92 introduziu os JOINs de forma a separar as junções das tabelas dos filtros utilizados. A partir do SQL92, a consulta abaixo era possível:

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes LEFT OUTER JOIN tblCarros
ON tblClientes.ClienteID = tblCarros.ClienteID

Quando o SQL92 foi finalizado, a recomendação geral era utilizar o LEFT OUTER JOIN em oposição a construções proprietárias como *= e (+). O fato de ser uma recomendação oficial não quer dizer que da noite pro dia, todos os analistas trocariam todas suas aplicações e os métodos de realizar consultas. Mesmo sendo um padrão, ainda se viu por muito tempo (e até os dias atuais) junções realizadas na cláusula WHERE e a utilização de extensões proprietárias.

Não existe absolutamente nenhuma diferença em termos de desempenho. Utilizar uma junção na cláusula WHERE ou via operadores JOIN tem exatamente o mesmo desempenho. Não sei se no passado isso fazia alguma diferença (acredito que não, pois, a semântica é a mesma). Ainda que houvesse alguma diferença no passado, os otimizadores de consulta atuais estão muito mais inteligentes e percebem que não há absolutamente nenhuma diferença. Isso é verdade para as junções do tipo INNER JOIN, mas e quando as junções do tipo OUTER JOIN ? Será que elas são exatamente iguais aos seus correspondentes proprietários ? Vejamos uma implementação em SQL Server.

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))

— 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’)

Exemplos práticos

Vejamos agora alguns exemplos práticos para testar os comportamentos dos operadores OUTER JOIN e seus correspondentes proprietários. As consultas estão categorizadas de acordo com algumas situações bem particulares do dia a dia. Se você estiver usando o SQL Server 2005 ou o 2008, lembre-se de colocar o modo em compatibilidade com o SQL Server 2000 (80). Isso pode ser feito nas propriedades do banco de dados ou através da stored procedure sp_dbcmptlevel.

Situação 1: Junção à esquerda sem cláusula WHERE

O objetivo das consultas abaixo é retornar a relação de todos os clientes e seus carros. Todos os clientes devem ser retornados quer tenham carros ou não. A primeira está em conformidade com o padrão ANSI92 (é ANSI Compliance) e a segunda utiliza os operadores proprietários do Transact SQL.

— Recuperar todos os clientes e os carros se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID

SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI, tblCarros AS CAR
WHERE
    CLI.ClienteID *= CAR.ClienteID

O resultado produzido é exatamente o mesmo:

Nome

Placa

Ano

Descrição

Ivone

JGK7044

2006

Peugeot 2006

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

O plano de execução também é exatamente o mesmo (era de se esperar):

Se os resultados são exatamente os mesmos, é recomendável utilizar o operador LEFT OUTER JOIN por estar na especificação ANSI ao invés de extensões proprietárias.

Situação 2: Junção à esquerda com cláusula WHERE para colunas da tabela à esquerda

O objetivo das consultas abaixo é retornar a relação de todos os clientes e seus carros. Todos os clientes devem ser retornados quer tenham carros ou não. Dessa vez, um filtro é colocado sobre a tabela de clientes, ou seja, é necessário retornar apenas os clientes que possuam a string "an" no nome. A primeira está em conformidade com o padrão ANSI92 (é ANSI Compliance) e a segunda utiliza os operadores proprietários do Transact SQL.

— Recuperar todos os clientes que tenham a string "an" nome e os carros se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID
WHERE
    Nome LIKE ‘%an%’

SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI, tblCarros AS CAR
WHERE
    CLI.ClienteID *= CAR.ClienteID AND Nome LIKE ‘%an%’

O resultado produzido é exatamente o mesmo:

Nome

Placa

Ano

Descrição

Amanda

KHZ0345

2007

Vectra GT

Amanda

BDC3211

2008

Fiat Stilo

Mariana

JGA7438

2008

Corsa Hatch

Regiane

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

O plano de execução também é exatamente o mesmo (era de se esperar):

Se os resultados são exatamente os mesmos, é recomendável utilizar o operador LEFT OUTER JOIN por estar na especificação ANSI ao invés de extensões proprietárias.

Situação 3: Junção à esquerda com cláusula WHERE para colunas da tabela à direita

O objetivo da próxima consulta é recuperar a relação de todos os clientes e os carros de 2006 se houver. Dessa vez o comportamento do LEFT OUTER JOIN e das extensões proprietárias podem fazer com que o resultado seja diferente.

— Recuperar todos os clientes e os carros de 2006 se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID
WHERE
    CAR.Ano = 2006

O resultado da consulta é:

Nome

Placa

Ano

Descrição

Ivone

JGK7044

2006

Peugeot 2006

O plano de execução dessa consulta foi:

Se executarmos a alternativa utilizando a versão proprietária, os resultados serão diferentes:

— Recuperar todos os clientes e os carros de 2006 se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI, tblCarros AS CAR
WHERE
    CLI.ClienteID *= CAR.ClienteID AND CAR.Ano = 2006

O resultado da consulta é:

Nome

Placa

Ano

Descrição

Ivone

JGK7044

2006

Peugeot 2006

Amanda

NULL

NULL

NULL

Mariana

NULL

NULL

NULL

Regiane

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

O plano de execução parece ser exatamente o mesmo:

Mas se o plano de execução parece ser exatamente o mesmo por que os resultados são diferentes ? E o que significa essa diferença ? Por que a primeira consulta retornou um registro e a segunda retornou 5 ? Embora o plano de execução pareça ser o mesmo ele não é. Há um detalhe visual que pode passar desapercebido. Na primeira consulta (a ANSI), o operador Clustered Index Scan superior está relacionado a PK_Carros e o Cluster Index Scan inferior está relacionado a PK_Cliente. Na segunda consulta (a das extensões) os operadores estão invertidos.

Na consulta ANSI, a junção entre clientes e carros é realizada primeiro e nesse caso são retornados todos os clientes e seus carros e caso haja algum cliente sem carro ele também é incluído no resultado (vimos isso posteriormente). Quando a cláusula WHERE especificou que o ano deve ser 2006, implicitamente admite-se que o carro tenha que existir, pois, somente os carros existentes podem ter o ano do carro comparado. Isso significa que todos os clientes que não possuem carros no ano de 2006 são descartados da consulta, pois, as colunas de carro estariam preenchidas com nulos. De um ponto de visto lógico, implicitamente o LEFT OUTER JOIN foi convertido para um INNER JOIN. De um ponto de vista físico, temos o overhead desnecessário do LEFT OUTER JOIN para uma consulta que poderia ser feita com o INNER JOIN, pois, apenas carro existentes e do ano de 2006 devem ser considerados.

Na consulta que utiliza as extensões proprietárias, o comportamento é um pouco diferente. Como o JOIN é feito via cláusula WHERE, o otimizador entende que a condição de junção é combinar a coluna ClienteID, mas adicionalmente filtrar os carros com ano de 2006. Isso significa que a tabela de carros é lida, os carros cujo o ano não seja igual a 2006 são descartados e um resultado intermediário é produzido (no caso somente o Peugeot 2006). Posteriormente esse resultado é combinado com a tabela de clientes em uma situação de LEFT OUTER JOIN, ou seja, retorna-se todos os clientes quer tenham correspondentes no resultado intermediário ou não. Como apenas Ivone tem um carro de 2006, os dados do carro aparecem. As demais clientes não possuem carros cujo ano é 2006 e portanto tem as colunas preenchidas com NULL.

Alguém pode perguntar: "Qual é o correto então ?". Em princípio, o "correto" não depende da sintaxe, mas sim do resultado esperado. O que é necessário mostrar ? O primeiro resultado ou o segundo resultado ? Já vi situações em que os dois são importantes. O que interessa não é tentar achar o "correto" (os dois podem ser corretos), mas sim identificar que as construções da forma como estão produzem resultados diferentes e que essa diferença precisa ser conhecida para que uma consulta não seja feita e apresente um resultado indesejado.

Outra pergunta que pode surgir é a respeito das extensões proprietárias. Se as extensões proprietárias devem ser evitadas, como então produzir o mesmo resultado sem elas ? Algumas tentativas podem ser elaboradas:

— Recuperar todos os clientes e os carros de 2006 se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    INNER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID
WHERE
    CAR.Ano = 2006

UNION ALL

SELECT
    Nome, NULL, NULL, NULL
FROM
    tblClientes AS CLI
WHERE NOT EXISTS
    (SELECT * FROM tblCarros AS CAR
    WHERE CLI.ClienteID = CAR.ClienteID AND Ano = 2006)

A consulta acima retorna todos os clientes que tem carros do ano 2006 e faz uma união com todos os clientes que não tem carros em 2006 (inclusive os que não tem carros). Ela não deixa de estar correta, mas considero um desperdídicio de recursos fazer algo tão trabalhoso e não performático. Embora as extensões proprietárias não devam mais ser utilizadas, podemos usar o raciocínio para produzir o resultado desejado. Quando as extensões proprietárias foram utilizadas, o filtro foi realizado na tabela de carros primeiro produzindo um resultado intermediário que foi combinado com a tabela de clientes. Podemos fazer o mesmo, apenas mudando o filtro de lugar.

— Recuperar todos os clientes e os carros de 2006 se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID AND Car.Ano = 2006

O resultado é idêntico ao das extensões proprietárias:

Nome

Placa

Ano

Descrição

Ivone

JGK7044

2006

Peugeot 2006

Amanda

NULL

NULL

NULL

Mariana

NULL

NULL

NULL

Regiane

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

Pode-se concluir que é possível evitar as extensões proprietárias para produzir o resultado acima. Só é preciso verificar o que é predicado de junção, ou seja, as condições que são usadas para juntar as tabelas e o que é filtro para eliminar registros indesejados após a junção. Quando os predicados são referentes à tabela da direita, eles devem ser colocados no JOIN, pois, se forem utilizados na cláusula WHERE, haverá a conversão implícita e nesse caso é mais performático usar o INNER JOIN. Todos as explicações são válidas para o operador RIGHT OUTER JOIN e sua extensão proprietária =*.

Toda migração para o SQL Server 2005 ou 2008 tem um ponto fundamental a ser decidido. Afinal mudar ou não o nível de compatibilidade ? Se mudarmos o nível de compatibilidade para 90 ou 100, poderemos utilizar novos recursos do TSQL como Row_Number, Common Table Expressions, Grouping Sets, etc. A contrapartida é que esses níveis de compatibilidade não toleram o uso das extensões proprietárias *= e =*. Ainda que o nível de compatibilidade 80 não impeça de se utilizar novos recursos do SQL Server 2005 / 2008 (Mirror, P2P Replication, Include Indexes, Transparent Data Encryption, etc), normalmente almeja-se utilizar os recursos novos do Transact SQL. Para que os novos recursos do TSQL possam ser utilizados, é preciso mudar o nível de compatibilidade e isso inclui substituir as construções *= e =*.

Se a migração for de um banco de dados complexo e com muitas regras de negócio, tome muito cuidado e não suponha que basta simplemente trocar *= e =* por LEFT OUTER JOIN e RIGHT OUTER JOIN. Como vimos, em algumas situações o resultado é o mesmo, mas em outras situações o resultado é diferente. E por incrível que pareça pouca gente sabe que tem diferença. Ainda vejo muitos consultores, DBAs e analista acharem que são sinônimos em todas as ocasiões. É preciso fazer as adaptações corretas.

[ ]s,

Gustavo

10 Respostas para “Será que LEFT OUTER JOIN, RIGHT OUTER JOIN, *= e =* são sinônimos ?

  1. Ola Máia.Li seu artigo e achei muito interessante!Só me surgiu uma dúvida. Na construção dessas revisões (da SQL89 para a SQL92) teve impactos em relação a performance?Ou seja, a diferença de utilizar os operadores antigos *= e =* com relação ao LEFT OUTER JOIN e RIGHT OUTER JOIN mudou alguma coisa em relação a ser mais rápido?abraçoClayton Santos

  2. Até q fim descobri para q serve o (*= e =*) huashuasuhhuasuasvlw…ps.: percebi que o artigo vai muito além do que descobri.parabéns.

  3. Olá Clayton,Não existe nenhuma diferença de desempenho entre o OUTER JOIN e os operadores *= e =*. O que mudar é a semântica já que nem sempre o resultado é o mesmo.Abs,

  4. Olá Maurício,Que bom que você descobriu mas vamos combinar de não utilizá-lo :)

  5. Olá Gustavo,Parabéns pelo artigo, muito bom e muito esclarecedor.É bom evitar o uso de extensões proprietárias para não complicar numa migração de um BD que não é simples como visto.Abraço.Wallace Pontes

  6. Gustavo,Sensacional este artigo…. parabéns…

  7. Oi Wallace,Realmente é bom evitar essas extensões. São as maiores complicações na hora de migrar do 2000 para uma versão superior ou ainda mudar o nível de compatibilidade.[ ]s,

  8. Oi Marcelo,Acho que de tanto ver aquela dúvida, me inspirei para escrevê-lo.Valeu pelo comentário e pela indicação.[ ]s,

  9. Gustavo, como faço para usar os operadores em um SQL2008?
    Excelente artigo!!!

    • Olá Everton,

      Se você estiver em 2008 com modo de compatibilidade 2000 eles vão funcionar. Se o modo de compatibilidade for 90 (2005) ou superior, infelizmente você terá de removê-los.

      [ ]s,

      Gustavo

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