First time caller here.

I have a question regarding best practices when joining tables. For example, both of the following queries return the same results:

SELECT i.id, p.first_name, p.last_name
FROM individuals i, profiles p
WHERE i.id = p.individual_id;

SELECT i.id, p.first_name, p.last_name
FROM individuals i INNER JOIN profiles p ON i.id = p.individual_id;

What are the pros / cons of these two approaches? Please let me know what you think. I'm interested in performance differences, but also in readability of the query, portability from one RDMS to another, etc.

Thank you!

  • JOINS are preferred. Mainly because that is the standard way of doing things and expresses your intent better. Performance-wise, I think it's a wash, after the optimizer gets done with it the execution plan would be pretty much the same. But I'm not a performance expert. – Darrel Lee Jul 23 '16 at 22:39
  • Someone can easily search if he wants to know where all are JOIN queries. But with equal sign it's difficult. Point is JOIN is more readable. – Gaurava Agarwal Jul 24 '16 at 9:54
  • Related: Index performance on ON versus WHERE – Tom V Jul 25 '16 at 11:06

Both queries are exactly the same, should return exactly the same result set and should produce exactly the same Execution plan.

BUT the second query is using ANSI-92 SQL syntax . The first one is using the older (very old) version of SQL Joins.

I can think of a few reasons why you should use the ANSI-92 SQL Syntax.

  1. It separates the Join Conditions and the actual filters in the WHERE clause if you have any.
  2. With the old syntax, you can only have the equivalent of INNER and CROSS joins. Outer joins were not possible except with complicated UNION queries. Some DBMS had added proprietary syntaxes for outer joins, (+) in Oracle, *= and =* in SQL Server, but these are being or have been deprecated. The explicit LEFT, RIGHT and FULL outer joins were added in the standard and have been adopted from almost all DBMS.
  3. It make query much more readable hence easier to debug.
  4. Now days most of the developers, DBAs and also ORMs use the Join syntax.
  5. When using ANSI-92 SQL Syntax for joins if you miss out a join condition you get a compile time error, hence it forces you to provide a valid join condition whereas in older version it doesn't throw any error but it simply returns a CROSS JOIN, in my experience this is one of the worst cons of the older approach.
  6. When using older version of joins and your query is mixing inner join and outer joins the result set may always not what you expect it to be.
| improve this answer | |
  • Great explanation. Thank you - exactly what I was looking for. – Steven L. Jul 24 '16 at 0:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.