SQL - When to use joins?

Question

Possible Duplicate:
SQL JOIN: is there a difference between USING, ON or WHERE?

Which query is better?

1.

  SELECT awardCateg.ID,
    awardCateg.DESCRIPTION,
    awardCateg.FREQUENCY_ID,
    freq.ID,
    freq.DESCRIPTION
   FROM AWARD_CATEGORY awardCateg
   INNER JOIN FREQUENCY freq ON awardCateg.FREQUENCY_ID=freq.ID

2.

  SELECT awardCateg.ID,
    awardCateg.DESCRIPTION,
    awardCateg.FREQUENCY_ID,
    freq.ID,
    freq.DESCRIPTION
   FROM AWARD_CATEGORY awardCateg,
        FREQUENCY freq
   WHERE awardCateg.FREQUENCY_ID=freq.ID

AWARD_CATEGORY:
ID  FREQUENCY_ID    DESCRIPTION
6   1   Spark of the month
7   2   Best Mentor of the quarter
9   3   Best Associate 
10  3   Leadership Award

FREQUENCY:
ID  DESCRIPTION
1   Monthly
2   Quarterly
3   Annual

Expected result:
ID  DESCRIPTION     FREQUENCY_ID    ID  DESCRIPTION
6   Oscar of the month            1 1   Monthly
7   Catalyst of the quarter 2   2   Quarterly
9   Best of the Best              3 3   Annual
10  Leadership Award              3 3   Annual

The two are functionally equivalent and performance should be identical, but the first is the more modern, preferred explicit join syntax. The implicit join (comma-separated FROM clause) is less readable. — Michael Berkowski, Jan 17 '13 at 13:56
Both functionally same, I would prefer the first one because its just more readable to most SQL programmers, seasoned and new — Aniket Inge, Jan 17 '13 at 14:00
Whenever you're unsure as to which query works faster, check their execution plans. All major RDBMS allow this. — toniedzwiedz, Jan 17 '13 at 14:17
Based on this post - http://stackoverflow.com/questions/3412785/sql-inner-join-question "Inner join will fetch you only those records that have a matching record in the child table. If there is no matching record in child table that particular row is excluded from query result." Suppose if the FREQUENCY.DESCRIPTION is empty for one of the rows, will I be able to fetch that row using INNER JOIN? — Radhika, Jan 17 '13 at 14:20
I think Aaron Bertrand just about covers everything you would need to know in [this post](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — GarethD, Jan 17 '13 at 16:03

Gordon Linoff · Answer 1 · 2013-01-17T16:58:19.510

1

You don't define what "better" is. In most databases, the two will optimize to the same underlying execution plan.

However, the first version is better for a variety of readable reasons:

(1) It is clear that an inner join is taking place.

(2) The , in a from clause implies a cartesian product/cross join. This is a very expensive operation.

(3) If you remove the , from the from clause in the second example, it is still a valid from clause . . . a query might run but not return the expexted results.

(4) The second syntax does not provide a standard syntax to support outer joins.

edited Jan 17 '13 at 16:58

answered Jan 17 '13 at 15:15

Gordon Linoff

1,242,037
58
646
786

Please don't take this as a defence for using ANSI-89 joins, it is not I abhor them, and would not encourage anyone to use them ever, but point 4 is not strictly true on all DBMS `SELECT * FROM A LEFT JOIN B ON A.ID = B.ID` could be written as `SELECT * FROM A, B WHERE A.ID *= B.ID`. – GarethD Jan 17 '13 at 16:01
@GarethD . . . I consider that an extension specific to some databases. Oracle, for instance, uses `(+)`. – Gordon Linoff Jan 17 '13 at 16:58

score 0 · Answer 2 · answered Jan 17 '13 at 13:56

Both of the queries have the same result.

The first one is the ANSI SQL-92 syntax of INNER JOIN while the second is the old one. The second query produces cartesian product but provided a condition of equality that's why it gives the same result as the query 1.

score -3 · Answer 3 · answered Jan 17 '13 at 13:59

-3

Inner Join will be faster as compared to Cartesian product. First query is better.

answered Jan 17 '13 at 13:59

Behroz Sikander

3,885
3
22
36

Incorrect, with SQL Server 2008, it works OK, no performance hit(BTW, I am not the downvoter) – Aniket Inge Jan 17 '13 at 13:59
Not correct. Performance is the same in most DB engines. – xlecoustillier Jan 17 '13 at 14:00
Thank you for correcting my knowledge. Any helping link that tells about the comparison of inner and cartesian ? – Behroz Sikander Jan 17 '13 at 14:02
On the cartesian product part, you're right. The thing is that most DB engines will use other techniques to filter the data, even in the second case, and won't use cartesian product. – xlecoustillier Jan 17 '13 at 14:07

SQL - When to use joins?

3 Answers3