Why is this code not recommended over the join clause in SQL?

Get Help SQL
#1

I found this code as an alternative to the join clause, and the guy states

Once again, please note that while the syntax above works great and still produces similar efficient results for Inner Join and Cross Join, it is not preferred ANSI standard syntax. It is suggested that for clarity of the code and to follow the standard, it is a good idea to use the explicit JOIN Keywords.

Clarity? Isn’t this understandable?

SELECT *
FROM TableA a, TableB b
WHERE a.condition1 = b.condition2

Over this:

SELECT *
FROM TableA
JOIN TableB 
ON TableA.condition1 = TableB.condition2

I’ve done more digging but no understandable explanation why not to use this. Help?

#2

Your example is a very straightforward query, though.

Consider whether the same syntax, of having the “join” as a condition in the WHERE clause, would be equally clear if we were dealing with a more complex query.

1 Like
#3

I don’t really have a large knowledge of SQL yet, but are you saying that in more complex queries it would be better to use JOIN over WHERE? Because of readability?

#4

Yes and no.

What I am saying is that your example is trivial. You have two tables, from which you are retrieving all the records, so the execution plan is probably identical for both. Which one you choose likely will have no impact on either readability nor execution.

If we consider a more complex query where we need to join… let’s say 90 tables and have numerous filtering conditions in the WHERE clause, do you think the query would suffer no impact to legibility by shifting those JOINs into the WHERE clause?

This assumes that we could even move the JOINs in the first place, as I don’t think that every type of JOIN could be easily replicated inside of WHERE…

@coffeencake might be able to offer some more insight, as I think they’re far more familiar with SQL than I am. :slight_smile:

4 Likes
#5

Good question. It really depends on the RDBMS you are using and how closely they are following the ANSI Standards. They aren’t going to make a difference performance wise and I could argue that I prefer the implicit join better.

For lessons, it’s important to learn the ANSI standard. In practice, adopt the development standards in place.

(In Oracle, I wouldn’t want you to come along and replace all my IMPLICIT joins with EXPLICT joins. :rofl:)

5 Likes
#6

I don’t have a great deal of experience with Oracle DB… but all the Microsoft SQL I’ve come across in my time has been full of various types of JOIN. :smiley:

1 Like
#7

This makes sense. Using JOIN when querying on multiple tables would certainly be better understood and more efficient.
I found this code which gets progressively more difficult (to me at least!) to understand.

SELECT table1.column1, table2.column2 
FROM table1, table2 
WHERE table1.column1 = table2.column1;

SELECT table1.column1, table2.column2 
FROM table1, table2, table3 
WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1;

SELECT table1.column1, table2.column2, table3.column1, table4.column9
FROM table1, table2, table3 
WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1 AND table4.column9 = table.column1; #eek!

it gets more and more complicated and now, after thinking to myself, I would really want to stick to JOIN for these things.

I do have one question however. Can we use WHERE in simple situations when joining tables, or is it completely forbidden from using it?

#8

:slight_smile: Are you following ANSI standards? If YES then use JOIN, else, use WHERE.

An important factor is also consistent formatting.

3 Likes
#9

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.