Which SQL do you write?

Question

When joining two tables, what are the difference between the two blocks below and which is the better approach?

Pattern A:

SELECT ...
FROM A
    INNER JOIN B
        ON A.PK = B.FK
WHERE 1=1
    AND A.Name = "Foo"
    AND B.Title = "Bar"

Pattern B:

SELECT ...
FROM A
    INNER JOIN B
        ON A.PK = B.FK
            AND B.Title = "Bar"
WHERE 1=1
    AND A.Name = "Foo"

Answer 1

This is going to differ from person to person, but I think that Pattern A is better.

What it does is it separates the table level joins from the filters. This could be helpful for queries with multiple joins and multiple filters because it clearly separates the two types of joining that is going on.

Answer 2

I prefer pattern A, but there is probably not any difference. You really have to look at the execution plan though to make sure it is running efficiently.

Answer 3

I may be wrong on this, but i see the 1st approach as a more efficient one. Your queries are executed inside-out. So when you run your join first, it'll run faster just because it has to match on (probably indexed) PK & FK Fields and not anything extraneous like your Title field ( which is probably a varchar, making the match even slower). So, by the time you get to your filtering where clause, you have less data to perform matching on. Total speculation though. I wonder what others would say. Wouldn't hurt to see the execution plan on this :)

Answer 4

If you replace INNER JOIN with OUTER JOIN, there will be difference.

Otherwise, these queries:

SELECT  ...
FROM    A
INNER JOIN
        B
ON      A.PK = B.FK
WHERE   A.Name = "Foo"
        AND B.Title = "Bar"

SELECT  ...
FROM    A
INNER JOIN
        B
ON      A.PK = B.FK
        AND B.Title = "Bar"
WHERE   A.Name = "Foo"

SELECT  *
FROM    A, B
WHERE   B.Title = "Bar"
        AND A.Name = "Foo"
        AND A.PK = B.FK

are identical.

Oracle, MySQL, PostgeSQL and SQL Server will treat them exactly the same, and use exactly same plan for all of them.

I'd use this one:

SELECT  ...
FROM    A
INNER JOIN
        B
ON      B.FK = A.PK
WHERE   A.Name = "Foo"
        AND B.Title = "Bar"

if there is a single-column key on B.FK, and this one:

SELECT  ...
FROM    A
INNER JOIN
        B
ON      B.FK = A.PK
        AND B.Title = "Bar"
WHERE   A.Name = "Foo"

if there is a composite key on (B.FK, B.title).

The join conditions are more visual in this case.