Just curious about something - not project related

Hello, everyone,

I’ve got a question that has been in the back of my mind for quite some time, and didn’t really know where else to turn. :smile:

When selecting data from two tables, what is the difference (and which is the best way) between just listing tables and using an actual JOIN statement?

IE:

SELECT t1.id, t1.name, t2.description
FROM t1,t2
WHERE t2.name = t1.name

versus

SELECT t1.id, t1.name, t2.description
FROM t1 JOIN t2 ON t2.name = t1.name

Thank you,

V/r,

:slight_smile:

I would say they’re the same. The first uses the traditional way (or the previous standard) and the second the modern way

Is one not more efficient or accurate than the other?

V/r,

:slight_smile:

Somewhere long ago I’ve read that some optimizers are a bit faster when you use JOINS instead of conditions but I don’t know if that’s true.

In terms to accuracy, both are the same. The only real difference is that it is easier to write when you use conditions but then your WHERE clause can get really cluttered with thousands of conditions between the relationship and the filters.

Using JOINS is harder but provides a cleaner vision of your filers since the relationshiops will be with the JOINS

2 Likes

Statement two is technically more efficient because the dbms will convert statement one to statement two before execution, but the difference is negligible.

The real benefit to the second approach is readability and standardization. It’s often easier to understand and debug statement two (and this from someone who used the first format FOREVER)

1 Like

Of course, Informix is the exception to the rule. Whenever I use a Join, it converts it to condition. But Informix is always an exception to almost any rule. I found this the hard way sigh

Thanks, @molona and @DaveMaxwell, for the input. I’ve never been a fan of the first method, I’ve always gone with the format of the second method. But I am increasingly running across queries of the first variety.

V/r,

:slight_smile:

That’s interesting because that’s a VERY old school approach, and has basically been taught out of schools for quite a while.

1 Like

They’re just two different ANSIs. I think that JOINS are included in ANSI-92 and the first ANSI-89. But again, this is something that I’ve read looooooong ago and I don’t even remember where :stuck_out_tongue:

Might even be ANSI-72…

I just responded to another post in the Databases forum - the OP presented their query using the first method. That’s what got me really thinking about this question. I kind of thought it was old school - but I see many forum questions where the query uses the old-school format. (shrug)

:slight_smile:

Either one works, but in the long run, the join syntax ends up being easier to read and debug. And the more tables you want to join, the more advantageous the join syntax becomes, not to mention if you want to do outer joins

1 Like

Also, it depends on the database you use. You get used to that syntax.

Each database has its own SQL and tricks. How much they follow the standard is up to them although, in general, for simple queries, I would say that they do follow it and then expand on it.

Yet, some databases prefer one notation to the other.

Informix (IBM) only uses conditions to create relationships among tables. Someone used to that syntax will prefer that notation. DB2 (also IBM and much more extended) is quite similar to Informix although is more standard compliant, I think.

Oracle is a different world.

when you’re doing an inner join, they are equivalent – although, as others have said, you’d be nuts to keep writing the old way

when you’re doing an outer join, though, you’d be ~insane~ to use the old way

2 Likes

I’m not even sure I could even remember HOW to do that (some sort of mild computer-centric PTSD, I do believe)

1 Like

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