Joining Tables in the FROM vs. the WHERE clause

Question

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!

score 7 · Accepted Answer · edited Jul 24 '16 at 06:45

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.

It separates the Join Conditions and the actual filters in the WHERE clause if you have any.
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.
It make query much more readable hence easier to debug.
Now days most of the developers, DBAs and also ORMs use the Join syntax.
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.
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.

Joining Tables in the FROM vs. the WHERE clause

1 Answers1