SQL deixou a associação vs várias tabelas na linha FROM?

A maioria dos dialetos SQL aceita ambas as seguintes consultas:

SELECT a.foo, b.foo FROM a, b WHERE ax = bx SELECT a.foo, b.foo FROM a LEFT JOIN b ON ax = bx 

Agora, obviamente, quando você precisa de uma junit externa, a segunda syntax é necessária. Mas ao fazer uma junit interna, por que eu deveria preferir a segunda syntax à primeira (ou vice-versa)?

A syntax antiga, apenas listando as tabelas e usando a cláusula WHERE para especificar os critérios de junit, está sendo reprovada na maioria dos bancos de dados modernos.

Não é apenas para mostrar, a syntax antiga tem a possibilidade de ser ambígua quando você usa junções INNER e OUTER na mesma consulta.

Deixe-me lhe dar um exemplo.

Vamos supor que você tenha 3 tabelas no seu sistema:

 Company Department Employee 

Cada tabela contém numerosas linhas, ligadas entre si. Você tem várias empresas, e cada empresa pode ter vários departamentos, e cada departamento pode ter vários funcionários.

Ok, agora você quer fazer o seguinte:

Listar todas as empresas e include todos os seus departamentos e todos os seus funcionários. Observe que algumas empresas ainda não possuem departamentos, mas certifique-se de incluí-las também. Certifique-se de recuperar somente departamentos que tenham funcionários, mas sempre liste todas as empresas.

Então você faz isso:

 SELECT * -- for simplicity FROM Company, Department, Employee WHERE Company.ID *= Department.CompanyID AND Department.ID = Employee.DepartmentID 

Observe que o último deles é uma junit interna, para preencher os critérios que você deseja apenas departamentos com pessoas.

Ok, então o que acontece agora. Bem, o problema é que isso depende do mecanismo de database, do otimizador de consulta, dos índices e das statistics da tabela. Deixe-me explicar.

Se o otimizador de consultas determinar que a maneira de fazer isso é primeiro pegar uma empresa, localizar os departamentos e, em seguida, fazer uma junit interna com os funcionários, você não obterá nenhuma empresa que não tenha departamentos.

A razão para isso é que a cláusula WHERE determina quais linhas acabam no resultado final, não partes individuais das linhas.

E nesse caso, devido à junit à esquerda, a coluna Department.ID será NULL e, portanto, quando se trata do INNER JOIN to Employee, não há como preencher essa restrição para a linha Employee, e, portanto, não aparecer.

Por outro lado, se o otimizador de consulta decidir atacar primeiro o departamento-funcionário e, em seguida, fazer uma junit à esquerda com as empresas, você as verá.

Então a antiga syntax é ambígua. Não há como especificar o que você deseja, sem lidar com dicas de consulta, e alguns bancos de dados não têm nenhuma maneira.

Digite a nova syntax, com isso você pode escolher.

Por exemplo, se você quiser todas as empresas, como a descrição do problema afirma, isso é o que você escreveria:

 SELECT * FROM Company LEFT JOIN ( Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID ) ON Company.ID = Department.CompanyID 

Aqui você especifica que deseja que a associação departamento-funcionário seja feita como uma só e, em seguida, junte os resultados dela com as empresas.

Além disso, digamos que você queira apenas departamentos que contenham a letra X em seu nome. Novamente, com o estilo antigo, você corre o risco de perder a empresa também, se não tiver departamentos com um X em seu nome, mas com a nova syntax, você pode fazer isso:

 SELECT * FROM Company LEFT JOIN ( Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%' 

Essa cláusula extra é usada para a junit, mas não é um filtro para a linha inteira. Assim, a linha pode aparecer com informações da empresa, mas pode ter NULLs em todas as colunas de departamento e funcionário para essa linha, porque não há departamento com um X em seu nome para essa empresa. Isso é difícil com a antiga syntax.

É por isso que, entre outros fornecedores, a Microsoft descontinuou a antiga syntax de junit externa, mas não a antiga syntax de junit interna, desde o SQL Server 2005 e superior. A única maneira de se comunicar com um database em execução no Microsoft SQL Server 2005 ou 2008, usando a syntax de junit externa do estilo antigo, é definir esse database no modo de compatibilidade 8.0 (também conhecido como SQL Server 2000).

Além disso, a maneira antiga, lançando um monte de tabelas no otimizador de consulta, com um monte de cláusulas WHERE, era semelhante a dizer “aqui está você, faça o melhor que puder”. Com a nova syntax, o otimizador de consultas tem menos trabalho a fazer para descobrir quais partes vão juntas.

Então você tem isso.

LEFT e INNER JOIN é a onda do futuro.

A syntax JOIN mantém as condições próximas à tabela à qual elas se aplicam. Isso é especialmente útil quando você associa uma grande quantidade de tabelas.

By the way, você pode fazer uma junit externa com a primeira syntax também:

 WHERE ax = bx(+) 

Ou

 WHERE ax *= bx 

Ou

 WHERE ax = bx or ax not in (select x from b) 

A primeira maneira é o padrão mais antigo. O segundo método foi introduzido no SQL-92, http://en.wikipedia.org/wiki/SQL . O padrão completo pode ser visto em http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt .

Levou muitos anos até que as empresas de database adotassem o padrão SQL-92.

Então a razão pela qual o segundo método é o preferido, é o padrão SQL de acordo com o comitê de padrões ANSI e ISO.

O segundo é o preferido porque é muito menos provável que resulte em uma união cruzada acidental, esquecendo-se de colocá-lo na cláusula where. Uma junit sem cláusula on falhará na verificação de syntax, uma junit de estilo antigo com nenhuma cláusula where não falhará, fará uma junit cruzada.

Além disso, quando mais tarde você tiver uma junit à esquerda, será útil para a manutenção que todas estejam na mesma estrutura. E a antiga syntax está desatualizada desde 1992, já passou da hora de parar de usá-la.

Além disso, descobri que muitas pessoas que usam exclusivamente a primeira syntax não entendem realmente que junções e compreensão de junções são essenciais para obter resultados corretos ao consultar.

Basicamente, quando sua cláusula FROM lista tabelas assim:

 SELECT * FROM tableA, tableB, tableC 

o resultado é um produto cruzado de todas as linhas nas tabelas A, B, C. Em seguida, você aplica a restrição WHERE tableA.id = tableB.a_id que elimina um grande número de linhas, e depois … AND tableB.id = tableC.b_id e você deve obter apenas as linhas nas quais realmente está interessado.

Os SGBDs sabem como otimizar esse SQL, de modo que a diferença de desempenho para gravar isso usando JOINs seja insignificante (se houver). Usando a notação JOIN torna a instrução SQL mais legível (IMHO, não usando junções transforma a declaração em uma bagunça). Usando o produto cruzado, você precisa fornecer critérios de junit na cláusula WHERE, e esse é o problema com a notação. Você está enchendo sua cláusula WHERE com coisas como

  tableA.id = tableB.a_id AND tableB.id = tableC.b_id 

que é usado apenas para restringir o produto cruzado. A cláusula WHERE deve conter apenas RESTRICTIONS para o conjunto de resultados. Se você combinar critérios de junit de tabela com restrições de conjunto de resultados, você (e outros) achará sua consulta mais difícil de ler. Você deve definitivamente usar JOINs e manter a cláusula FROM uma cláusula FROM, e a cláusula WHERE uma cláusula WHERE.

Eu acho que existem algumas boas razões nesta página para adotar o segundo método – usando JOINs explícitas. O argumento é que, quando os critérios de JOIN são removidos da cláusula WHERE, fica muito mais fácil ver os critérios de seleção restantes na cláusula WHERE.

Em instruções SELECT realmente complexas, fica muito mais fácil para um leitor entender o que está acontecendo.

A syntax SELECT * FROM table1, table2, ... está ok para algumas tabelas, mas torna-se mais exponencial ( não necessariamente uma declaração matematicamente precisa ) cada vez mais difícil à medida que o número de tabelas aumenta.

A syntax JOIN é mais difícil de escrever (no início), mas torna explícito que critérios afetam quais tabelas. Isso torna muito mais difícil cometer um erro.

Além disso, se todas as junções forem INNER, as duas versões serão equivalentes. No entanto, no momento em que você tem um EXTERIOR em qualquer lugar da declaração, as coisas ficam muito mais complicadas e é virtualmente garantido que o que você escreve não estará consultando o que você acha que escreveu.

Quando você precisa de uma junit externa, a segunda syntax nem sempre é necessária:

Oráculo:

 SELECT a.foo, b.foo FROM a, b WHERE ax = bx(+) 

MSSQLServer (embora tenha sido descontinuado na versão 2000) / Sybase:

 SELECT a.foo, b.foo FROM a, b WHERE ax *= bx 

Mas voltando à sua pergunta. Eu não sei a resposta, mas provavelmente está relacionado ao fato de que uma junit é mais natural (pelo menos sintaticamente) do que adicionar uma expressão a uma cláusula where quando você está fazendo exatamente isso: unindose .

Eu ouço muitas pessoas reclamando que a primeira é muito difícil de entender e que não está claro. Eu não vejo um problema com isso, mas depois de ter essa discussão, eu uso o segundo mesmo no INNER JOINS para maior clareza.

Para o database, eles acabam sendo os mesmos. Para você, porém, você terá que usar essa segunda syntax em algumas situações. Para editar consultas que acabam tendo que usá-las (descobrindo que você precisava de uma junit esquerda onde você tinha uma junit direta), e para consistência, eu padronizaria apenas no segundo método. Isso tornará as consultas de leitura mais fáceis.

Bem, a primeira e segunda consultas podem produzir resultados diferentes, porque um LEFT JOIN inclui todos os registros da primeira tabela, mesmo que não haja registros correspondentes na tabela à direita.