SQL Query JOIN Performance

Question

I wanted to know which is better in terms of performance: to put conditions in a CONNECTION? (x JOIN y on x.hi = y.hi AND ....) or perhaps put it under WHERE and leave JOIN only with the intersection condition.

thank you people

Answer 1

This will depend on the query optimization mechanism and how it splits the query into its logical query operators.

With MS SQL Server you can view the execution plan, but usually there is no difference, as the optimizer will see both ways as equivalent.

Answer 2

StackOverflow here.

, . , SQL .

Answer 3

, ( ), , .

SELECT  a.field1
        , a.field2
        , b.field3
FROM  table1 a
LEFT JOIN table2 b
    ON a.id = b.id
WHERE    a.field5 = 'test'
        AND b.field3 = 1

SELECT  a.field1
        , a.field2
        , b.field3
FROM table1 a
LEFT JOIN table2 b
    ON a.id = b.id AND b.field3 = 1
WHERE a.field5 = 'test'

, , where , .

Answer 4

, *= =* OUTER JOIN WHERE SQL Server 2008 R2

I would really like to see a query mixing 2 styles