When must i be utilising JOIN ON or Whereby a predicament such as 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 am conscious that the very first query may have one PhoneNumber column, as the second may have two. Will this considerably modify the speed from the query?
Syntax errors aside, you are evaluating ANSI-89 JOIN syntax (JOIN criteria within the WHERE clause) to ANSI-92 JOIN syntax (uses the JOIN keyword).
They perform just like each other, but ANSI-89 lacks OUTER JOIN support a lot of databases have custom way of showing 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.
To have an inner join it does not matter towards the results whether predicates are make the join condition or where clause.
I'd put stuff associated with joining the tables within the join clause and stuff associated with blocking within the where clause.
SELECT * FROM Persons JOIN Employee ON Persons.PhoneNumber = Employee.PhoneNumber WHERE Persons.PhoneNumber = @PhoneNumber
JOIN is the best choice for... joining tables realistically associated with foreign secrets. This is because you are able to setup different type of joins (left, right, inner, outer).
Where's more may well blocking of rows.
Inside your simple example, it can make no real distinction between the 2.