Início / dba / Perguntas / 125422
Accepted
boot4life
Asked: 2016-01-07 12:14:54 +0800 CST

O que a posição da cláusula ON realmente significa?

  • 772

A sintaxe normal JOIN ... ON ...é bem conhecida. Mas também é possível posicionar a ONcláusula separada daquela a JOINque corresponde. Isso é algo que raramente é visto na prática, não é encontrado em tutoriais e não encontrei nenhum recurso da web que sequer mencione que isso é possível.

Aqui está um script para brincar:

SELECT *
INTO #widgets1
FROM (VALUES (1), (2), (3)) x(WidgetID)


SELECT *
INTO #widgets2
FROM (VALUES (1, 'SomeValue1'), (2, 'SomeValue2'), (3, 'SomeValue3')) x(WidgetID, SomeValue)

SELECT *
INTO #widgetProperties
FROM (VALUES
    (1, 'a'), (1, 'b'),
    (2, 'a'), (2, 'b'))
x(WidgetID, PropertyName)


--q1
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 ON w2.WidgetID = w1.WidgetID
LEFT JOIN #widgetProperties wp ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
ORDER BY w1.WidgetID


--q2
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 --no ON clause here
JOIN #widgetProperties wp
 ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
 ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID


--q3
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN (
    #widgets2 w2 --no SELECT or FROM here
    JOIN #widgetProperties wp
    ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b')
ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID

q1 parece normal. q2 e q3 têm esses posicionamentos incomuns da ONcláusula.

Este script não necessariamente faz muito sentido. Foi difícil para mim inventar um cenário significativo.

Então, o que esses padrões de sintaxe incomuns significam? Como isso é definido? Percebi que nem todas as posições e ordenações para as duas ONcláusulas são permitidas. Quais são as regras que regem isso?

Além disso, é sempre uma boa ideia escrever consultas como esta?

sql-server t-sql

2 respostas

  1. Ele determina as tabelas lógicas envolvidas na junção.

    Com um exemplo simples

    SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2
         ON w2.WidgetID = w1.WidgetID
       JOIN #widgetProperties wp
         ON w2.WidgetID = wp.WidgetID
            AND wp.PropertyName = 'b'
ORDER  BY w1.WidgetID

    #widgets1é deixada externamente unida a #widgets2- o resultado disso forma uma tabela virtual que é unida internamente a #widgetProperties. O predicado w2.WidgetID = wp.WidgetIDsignificará que quaisquer linhas estendidas nulas da junção externa inicial serão filtradas, efetivamente tornando todas as junções internas.

    Isso difere de q2...

    SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2 --no ON clause here
                 JOIN #widgetProperties wp
                   ON w2.WidgetID = wp.WidgetID
                      AND wp.PropertyName = 'b'
         ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

    #widgets2é unido internamente em #widgetProperties. A tabela virtual resultante dessa junção é a tabela à direita na junção externa esquerda em#widgets1

    O mesmo resultado pode ser alcançado usando uma tabela derivada ou Common Table Expression...

    WITH VT2
     AS (SELECT w2.WidgetID,
                w2.SomeValue,
                wp.PropertyName
         FROM   #widgets2 w2 
                JOIN #widgetProperties wp
                  ON w2.WidgetID = wp.WidgetID
                     AND wp.PropertyName = 'b')
SELECT w1.WidgetID,
       VT2.SomeValue,
       VT2.PropertyName
FROM   #widgets1 w1
       LEFT JOIN VT2
         ON VT2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

    ... Ou, alternativamente, você pode reordenar as tabelas virtuais e usar um em seu RIGHT JOINlugar.

    SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets2 w2
       INNER JOIN #widgetProperties wp
               ON w2.WidgetID = wp.WidgetID
                  AND wp.PropertyName = 'b'
       RIGHT JOIN #widgets1 w1
               ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

    Isso é coberto por Itzik Ben Gan aqui

    ... as condições de JOIN devem seguir uma relação quiástica com a ordem da tabela. Ou seja, se você especificar as tabelas T1, T2, T3 e T4 nessa ordem e as condições JOIN corresponderem a T1 com T2, T2 com T3 e T3 com T4, você deverá especificar as condições JOIN na ordem oposta à ordem da tabela , assim:

    FROM   T1
       <join_type> T2 T2
                  <join_type> T3 T3
                             <join_type> T4
                               ON T4.key = T3.key
                    ON T3.key = T2.key
         ON T2.key = T1.key

    Olhando para esta técnica de junção de uma maneira diferente, uma determinada condição JOIN pode referir-se apenas aos nomes das tabelas logo acima dela ou aos nomes das tabelas que as condições JOIN anteriores já referiram e resolveram.

    mas o artigo contém várias imprecisões, veja também a carta de acompanhamento de Lubor Kollar .

    • 35
  2. Best Answer

    Se você observar o FROMdiagrama de sintaxe da cláusula , verá que há apenas um lugar para a ONcláusula:

    <joined_table> ::= 
{
    <table_source> <join_type> <table_source> ON <search_condition> 
    ...
}

    O que você acha confuso é a recursão simples, porque <table_source>em <joined_table> acima pode ser outro <joined_table>:

    [ FROM { <table_source> } [ ,...n ] ] 
<table_source> ::= 
{
    table_or_view_name ... 
    ...
    | <joined_table> 
    ...
}

    Para evitar confusão, você deve usar parênteses em casos não óbvios (como seus exemplos) para separar visualmente <table_sources>; eles não são necessários para o analisador de consulta, mas são úteis para humanos.

    • 33

relate perguntas