Which gives a faster query? Which is cleaner? JOIN ON or WHERE

Question

When should I be using JOIN ON or WHERE in a scenario like the one below?

DECLARE
  @PhoneNumber int = 5551234

-- JOIN ON
SELECT *
FROM Persons
JOIN Employees ON Persons.DateOfBirth = Employee.DateOfBirth AND
Persons.PhoneNumber = Employees.PhoneNumber
WHERE Persons.PhoneNumber = @PhoneNumber

-- WHERE
SELECT *
FROM Persons
JOIN Employees ON Persons.DateOfBirth = Employee.DateofBirth
WHERE Persons.PhoneNumber = @PhoneNumber AND Employees.PhoneNumber = @PhoneNumber

I'm aware that the first query will have one PhoneNumber column, while the second will have two. Will this significantly affect the speed of the query?

Answer 1

For an inner join it makes no difference to the results whether predicates are put in the join condition or where clause.

I'd put stuff related to joining the tables in the join clause and stuff related to filtering in the where clause.

SELECT *
FROM Persons
JOIN Employee ON Persons.PhoneNumber = Employee.PhoneNumber
WHERE Persons.PhoneNumber = @PhoneNumber

Answer 2

JOIN is the right choice for... joining tables logically connected with foreign keys. The reason is that you can set up different kind of joins (left, right, inner, outer).

WHERE is more a logical filtering of rows.

In your simple example, it makes no real difference between the two.

Answer 3

Syntax errors aside, you're comparing ANSI-89 JOIN syntax (JOIN criteria in the WHERE clause) to ANSI-92 JOIN syntax (uses the JOIN keyword).

They perform identical to one another, but ANSI-89 lacks OUTER JOIN support so many databases have custom means of indicating OUTER joins:

database     ANSI-89 OUTER JOIN syntax
------------------------------------
Oracle       t1.column = t2.column(+)
SQL Server   t1.column =* t2.column

For sake of portability and readability--use ANSI-92 syntax.