-3

Most RDBMSs support both syntaxes:

SELECT ...
FROM table1 t1 JOIN table2 t2
ON t1.jfield = t2.jfield

as well as

SELECT ...
FROM table1 t1, table2 t2
WHERE t1.jfield = t2.jfield

Personally, I prefer the latter version because of less verbosity but does it presents any difference in performance and query execution? I have seen people on this forum making remarks that the 2nd style is obsolete and does not perform as well.

amphibient
  • 29,770
  • 54
  • 146
  • 240
  • No question asked. What do you want, a detailed analysis of the two? Benchmarks? To know how SQL optimizes your query to see if they're different? If the 2nd style is actually obsolete? – Eli Gassert Nov 08 '12 at 19:59
  • I said "I wonder if it presents any difference in performance" – amphibient Nov 08 '12 at 20:01
  • I changed it to a sentence with a question mark for your convenience ... if you were unable to infer the question implicitly – amphibient Nov 08 '12 at 20:03
  • 1
    I prefer the first (ANSI 92) as unlike the second one (ANSI 89) it is impossible to miss out a join condition and cause an inadvertent cross join. – Martin Smith Nov 08 '12 at 20:06
  • possible duplicate of [ANSI joins versus "where clause" joins](http://stackoverflow.com/questions/1613304/ansi-joins-versus-where-clause-joins) – Martin Smith Nov 08 '12 at 20:08
  • 1
    What kind of server are you working in? If SQL Server and SSMS, type both queries into a new query window and hit CTRL+L. Yo'ull likely see they both have the same query plan. SQL is smart. However, in complex queries, you're not doing yourself any favors by using the second syntax. SQL will be more efficient as optimizing in the first version. – Eli Gassert Nov 08 '12 at 20:09

3 Answers3

3

There is no difference in performance. But a lot in robustness. Leave the WHERE clause out in the second example and you wind up with an unwanted cartesian product. Leave out the ON in the first example and you just get a syntax error. That might be obvious for a two table join, but once you have 10 or 15 tables, those errors are hard to spot.

Additionally when you start using outer joins, using LEFT OUTER JOIN will work across all DBMS, whereas any DBMS specific outer join syntax is just that: DBMS specific (and e.g. Oracle's (+) operator can't do everything a LEFT OUTER JOIN can do.

So do get used to using the JOIN keyword.

  • I upvoted you but have two important points to make. First, leaving out the `on` clause is allowed, at least in MySQL. Not ANSI compliant, not pleasaant, but not a syntax error. Second, what can you do with `left outer join` in Oracle that you cannot do with `(+)`? – Gordon Linoff Nov 08 '12 at 20:19
  • @Gordon: re MySQL: I am not suprised (given the way MySQL works, it will probably just pick random rows, right?) re: Oracle: try a self outer join using `(+)`, or try an outer join to a sub-set of the other table (the corresponding thing to `... ON l.id = r.id AND r.some_col = 42` –  Nov 08 '12 at 20:23
1

Have a look at the execution plans of both forms and you'll see that they are identical (certainly true for MSSQL but I would eat my hat if it isn't for all databases) Since execution plan is identical there will be no performance difference between the 2 forms.

The reason a lot of people (me included) try to rid the world of the 2nd form is because:

  • Joins describe a relationship between 2 tables
  • Where clauses define filters on the result set

Those 2 are functionally very different and using the 2nd form makes it much harder to read and understand queries.

Eddy
  • 5,320
  • 24
  • 40
0

I recommend using 1st syntax. Its do nicely code for after updates. 2nd syntax is really old and cannot prefer this because join all result from tables and after filter data. Maybe I'm wrong but i think so if you have a indexes and PK, you can get more performance with joins because SQL server can provide better support for this operation (such as sorting, filtering, subquery...etc). Where clause is for filtering your data final result.
JOINS are very powerfull tool because you can join only just filtered data if you want and not join all tables results. May be better for RAM or Cache.

1st - you can adding just filtered data as shown syntax below
SELECT A., B.
FROM table_a as A
INNER JOIN table_B as B ON B.id = A.id -- join table b
AND B.name = 'bob' AND B.active = 1 -- lets filter more and return only rows for clause

- next syntax shown subquery which you can also use, it join ONLY filtered rows and return ONLY specific columns of that table
SELECT a., b., c.flag
FROM table_a as a
INNER JOIN table_B as b ON b.id = a.id -- join table b
AND b.name = 'bob' AND b.active = 1 -- lets filter more and return only rows for clause
INNER JOIN (
SELECT id, flag
FROM table_C
WHERE active = 1
) as c ON c.id = a.id


Its a simple and more read and easy to maintain of code in future.

Look at this link, its isn't beautifull code?
http://www.dpriver.com/products/sqlpp/sqlexamples4.php

Than have x condition in WHERE clause ... ofc you can't use agregate and subquery here ... i recommend learn more about joins and you'll be happy programmer =]
http://www.w3schools.com/sql/sql_join.asp

hope helps

daremachine
  • 2,678
  • 2
  • 23
  • 34