Whats the Difference between InnerJoin a table and include it in the FROM clause?

What the difference between the two below SQL Statements (one uses INNER JOIN, and the second uses the from clause) (Performance, execution time..),

and is there any cases i must use one instead of the other?

SELECT Tbl1_Fld1, Tb开发者_开发知识库l2_Fld1 FROM DB1..TABLE1 
INNER JOIN DB2..TABLE1 
on DB1..TABLE1 .Tbl1_Fld1 = DB2..TABLE1.Tbl2_Fld1

SELECT Tbl1_Fld1, Tbl2_Fld1 FROM DB1..TABLE1,DB2..TABLE1 
WHERE DB1..TABLE1 .Tbl1_Fld1 = DB2..TABLE1.Tbl2_Fld1

In a perfect world, those should be equivalent except that the first one better documents what you want to achieve (join two tables and then search the result).

Alas, history, bugs, features, optimizers and other obstacles make this much more complicated than it needs to be.

Some databases simply don't support INNER JOIN even though it's a SQL standard syntax.

Other have bugs for certain data types, so the join won't work or will be very slow.

So in reality, you will have to run these with suitable test data to find out. There is no way to say for sure just by looking at the SQL. Sometimes, there isn't even a way to say for sure when you can run it because changes in the underlying data can have a huge impact (for example, Oracle can suddenly decide to ignore the index because too many rows in the table have been changed).

Given a sane query optimizer, there shouldn't be a difference between the two.

Use INNER JOIN if your engine supports it, basically for clarity of join condition vs filter separation

SELECT Tbl1_Fld1, Tbl2_Fld1
FROM
    DB1..TABLE1,DB2..TABLE1 
WHERE
    DB1..TABLE1 .Tbl1_Fld1 = DB2..TABLE1.Tbl2_Fld1 AND
    DB1..TABLE1 .Tbl1_Fld1 = 'foo' AND DB2..TABLE1.Tbl2_Fld1 = 1

vs

SELECT Tbl1_Fld1, Tbl2_Fld1
FROM
   DB1..TABLE1 
   INNER JOIN
   DB2..TABLE1 ON DB1..TABLE1 .Tbl1_Fld1 = DB2..TABLE1.Tbl2_Fld1
WHERE 
   DB1..TABLE1 .Tbl1_Fld1 = 'foo' AND DB2..TABLE1.Tbl2_Fld1 = 1

There should be no difference in execution for most engines

However, and there's always a "however", not all joins are INNER. What about an OUTER join? The *= and =* is deprecated in SQL Server for example.

It was in Books online in SQL Server 2000. Here is the quote

In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

So if you have a more complex query with both inner and outer joins then it's going to become confusing if you mix styles. So, add consistency to clarity mentioned above

No new queries should be written using the "From " syntax (really called an implicit join). And personally I would rewrite any I came into contact with because it is simply a bad coding practice.

This syntax is highly subject to error from accidental cross joins and harder to mainatin and harder to read correctly. If you need to add a left join later you may get incorrect results if you don't convert to explicit joins. And sometimes you need a cross join but with the implicit syntax, you don't know if the cross join was a mistake (a common one using this syntax) or deliberate, therefore the maintainer doesn't know whether to change it or not. It was replaced in 1992 for goodness sakes. There is no excuse for not using the explicit join syntax in 2010.