[Resolved] a join query using 5 tables examples???

sherdog

Ive been looking and looking for an example of a 5 table join. i have not been able to find one if someone could point me to one or maybe throw a simple 5 table join i would greatly appreciate it.

Sxooter

A 5 table join is just like a 4 table join, is just like a 3 table join etc...

select * from table1 join table2 on (table1.field=table2.field) left join table3 on (table1.field=table3.field) right join table4 on (table3.id=table4.id) join table5 on (table1.field=table5.field);

sherdog

Thank-you to me its confusing still but what u posted solved my problem! thanks again!

mystrymaster

what are the benefits of actually using the join syntax as opposed to using WHERE table1.fieldname = table2.fieldname AND etc...

Sxooter

sherdog: Glad to have helped.

mystrymaster: The main benefit of using the join on () syntax are easier readability and maintainability, since it moves the join syntax out of the where clause and right after the join keyword. This makes maintenance easier, since the two together are easier to spot.

In Postgresql 7.2 and 7.3, the join on() syntax constrained join order, i.e. the planner would join the tables in the exact order you specified in your query plan, which means that a poor choice up front (i.e.a join of 1,000,000 rows against 1,000,000 rows) would result in a lot of work for the database. As of version 7.4beta1, the planner now treats "explicit" join syntax (i.e. join on () syntax) the same as where clause syntax for optimization purposes, therefore you can now use the join on () and expect the planner to reorganize your join order to most efficient one.

The join on () syntax is newer, I think it was introduced in SQL92. So, on some older databases, you may find that join on () doesn't work, or doesn't work as well as using where clause joins.

Sxooter

sherdog: Sometimes it's easier to understand if you format your query in a more readable format, like this one:

select * 
from 
  table1 
  join table2 
    on (table1.field=table2.field) 
  left join table3
    on (table1.field=table3.field) 
  right join table4 
    on (table3.id=table4.id) 
  join table5 
    on (table1.field=table5.field)
  where table1.somefield='abc'
  and
  table2.someotherfield=45
;

sherdog

Yah that is more readable, what my problem is with joins cause im still pretty new to using them in multple tables is trying to understand what it is doing. I imagine after doing it a few times i will grasp the concept (hopefully) :eek: thanks for help guys i really do appreciate it!

Sxooter

what my problem is with joins cause im still pretty new to using them in multple tables

I know what you mean. Whenever my other development partners and I run into a sticky bit of SQL, we refer to it as a "whiteboard" problem. I.e. we're gonna have to stand in front of the white board for a while figuring it out 🙂 Good luck on SQL, and if you want to learn it inside and out, look for books like SQL for Smarties by Joel Celko.

mystrymaster

sxooter - thanks for the explanation very clear and concise - excellent.

As far as speed benefits for queries where I am pulling a field ID from one table and associating it with a record of that same ID in another table would a join be better or would the where clause work just as well?

Sxooter

That very much depends on your SQL engine. For Postgresql <v7.4, it's often better to put them in the where clause because the query planner can then rearrange things. For other databases, it may well be the opposite. Note that left / right joins pretty much HAVE to go in the on() syntax, as putting it in the where clause won't have the same effect.

mystrymaster

using MySQL which would you recomend?

Sxooter

Not a clue on which is better in MySQL. Sorry. You could always test it out and see which is faster.