where or join?

[NZ Joe]

Which is better?

I have to select data from 5 different table in an already existing large database (so I can't change it, I just have to live with it).

I'm not an SQL expert and I understand the construction of queries using where...and...and...etc better than join...join...join...where, but which is better?

Should I just put up with sloppy looking but functional code?

Or should I attempt to do joins?

[Thing]

Joins are a little confusing at first, but after awhile they become your best friend. Might as well get comfortable with them now and go ahead and use joins.

[KDesigns]

As mongoloid stated, joins are a tad confusing at first. However, if you are selecting from 5 different tables (especially frequently) you will find that JOIN will become one of your new best friends.

[NZ Joe]

Thanks, I'll study more

[Thing]

Here's a nice little example of using joins:

http://www.w3schools.com/sql/sql_join.asp

[r937]

JOIN syntax is confusing at first?

first time i've heard that

i think that by isolating each specific join condition away from all the other conditions would make it a lot less confusing

JOIN syntax is definitely better

for one thing, it's pretty hard to write an outer join with the WHERE syntax

[Thing]

JOIN syntax is confusing at first?

first time i've heard that

We weren't all born with a SQL book in our hands like you Rudy!

[r937]

actually, i did not learn sql until i was in my late thirties

but that was 17 years ago



and there was no JOIN syntax at that time

i learned the table list syntax first

[goughb]

The answer:

Use joins, they are much faster then making multiple query calls from a while statement.

[StephenBauer]

I think Rudy is on the mark with stating that a "many" table query is easier to read using "joins" than with "where/and" use. It helps in the way you format the query...especially when it comes to looking at it in the future.

I would prefer:

select
  a.selcola,
  b.selcolb,
  c.selcolc
from
  tableA a
inner join
  tableB b
on
  a.joincol = b.joincol
inner join
  tableC c
on
  a.joincol = c.joincol
where
  b.somecond = 'blah'

over:

select
  a.selcola,
  b.selcolb,
  c.selcolc
from
  tableA a, tableB b, tableC c
where
  a.joincol = b.joincol
  and a.joincol = c.joincol
  and b.somecond = 'blah'

I think you will see that as you add more tables and true WHERE conditionals, the "where/and" method can get ugly fast.

Also, when using "where/and" it is harder to discern the true WHERE conditionals (the "b.somecond = 'blah'" expression) from the necessary table-joining conditionals.

Finally, if you ever need to alter the type of table join, it is typically a bit easier to do with the "join" example as opposed to then having to possibly re-write most of the "where/and" query to get an outer join in there.

BTW, the "where/and" method is an implicit join.