Differences between using JOIN and WHERE 1

[M8tt]

The following two pieces of code seem to do the same thing, but I am curious as to which is the better to use? Could someone please summarise what the differences are and which, if any, is the 'better' one to use.

Many Thanks in advance.

Select EQUI.EQUNR
From EQUI
Inner join AFIH on AFIH.EQUNR = EQUI.EQUNR
Inner join AUFK on AFIH.AUFNR = AUFK.AUFNR

Select EQUI.EQUNR
From AUFK, AFIH, EQUI
Where AFIH.EQUNR = EQUI.EQUNR
AND AFIH.AUFNR = AUFK.AUFNR

 

[donutman]

The first one is clear the second isn't. That's why the first is currently the preferred.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Explicit JOIN (first code sample) is better because:

- query becomes more readable. JOIN is for joining tables, WHERE primarily for filtering over joined tables.
- not all join types can be simulated with WHERE clause. Note that old T-SQL join syntax (*= and =*) is not recommended.
- finally, if someone "bridges" WHERE clause (typo in query, extra OR clause, SQL injection), 2nd code will turn into Cartesian product

For query optimizer both code samples are identical.

 

[donutman]

Vongrunt, I think that #2 and #3 from your list are trumped up charges against the alternative construct although they are both true statements. Just because you can't simulate all join types isn't a reason not to use the construct. And just because it can turn into a Cartesian product if you use it incorrectly is almost laughable. The Update statement is a much more dangerous weapon when mis-coded.

Clarity is dispositive.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Hmm, but your third point is that it's easier to make that error while possibly using it e.g. in an Update statement. So #3 is, in fact, a valid reason not to use it. I should know better than to argue with you when I'm so good at arguing with myself.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

#2: WHERE variant basically transforms cross join into inner join. Because joins are essential in SQL, I like to keep them explicit. Plus when you have to change code, all joins can save some time. Recently I worked on one DB/app. Business logic went loose, and few foreign key columns were demoted from "required" to "not required". So I replaced "INNER" with "LEFT OUTER" at dozen places. Simple.

#3: yes, this one is bloated a bit. But as a positive side-effect (no matter how small) I welcome it.

 

[CrystalVis]

i worked with Oracle a while back. all joins are done in the WHERE clause. left/right join is indicate by *=/=*. i thought this is strictly Oracle prefer method. where in sql, all joins are in the FROM clause.

 

[SQLSister]

It really makes little or no difference when you have an inner join. However when you use an outer join, SQL server often misinterprets the join ( usually as a cross join which can be very bad interms of performance and results) and Microsoft says to avoid the construction for outer joins. Personally I find that many people who use the second join exclusively tend to have a less than complete understanding of joins in general. Intent is certainly clearer when you use the first construction.

Questions about posting. See faq183-874

 

[r937]

However when you use an outer join, SQL server often misinterprets the join ( usually as a cross join which can be very bad interms of performance and results) and Microsoft says to avoid the construction for outer joins

which construction is this referring to, the OUTER JOIN syntax or the funny thing with the asterisk?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[SQLSister]

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.

Questions about posting. See faq183-874

 

[r937]

thanks

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

There are other reasons to use JOIN, which include the problem of excluding rows in the outer-joined table that meet a certain condition.

Should I use JOIN or WHERE to join tables? faq183-5168

--Goal: exclude deadbeat accounts, determined by 'Never Paid' in additional-info-table T2.

SELECT
   FROM T1, T2
   WHERE
      T1.ID *= T2.ID
      AND T2.Status = 'Never Paid'
      AND T2.ID IS NULL

--Oops: conflicting conditions that yield no records.

SELECT
   FROM T1
      LEFT JOIN T2 ON T1.ID = T2.ID AND T2.Status = 'Never Paid'
   WHERE
      T2.ID IS NULL

--Now records are returned

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[donutman]

When you come back to a forum you don't mess around.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Crazyec]

The using of joinnings will have better performance actually.