Taking a Intro to DB class (ORACLE), my teacher is crazy!

Hello everyone! I just wanted to share some information regarding the SQL language and this "new" way of doing queries.

I am still very anti-this-way as I do not like it at all, and I am wondering if this is how anyone else runs their queries (thus proving my Professor isn't as crazy as I like to think).

Ok, what I am explaining here is in the use of JOINS, so hold on tight if you don't know what I am talking about.

I have always done JOINED queries a specific way.

lets say we have the tables accounts, orders, comments

The normal way I do a query is like this

I select from the accounts table
JOIN orders
JOIN comments
WHERE accounts.account_id equals account we want

(Yes, this is spaghetti code :grin


Now, aparently this "new" way of doing queries should be done without a WHERE clause, and everything should be specified in the JOIN somewhere. As it is, it does work this way, however I tend to like to use the WHERE to restrict from the original table call.

New way:
select from the accounts table
JOIN orders
JOIN comments AND accounts.account_id = 'whatwewant';




Comments?

 

Yes very very true.

As the saying goes, do what works the best (and the most efficient)!!

 

I've never seen a join like that... I've seen join ... on (...) but this would be a first for that kind of "where." Any idea on the performance difference (if any) or is it just a syntactical style?

 

yip, we did some tests on mysql for instance , where a join like

SELECT b.detail, c.other_detail FROM tableb b, tablec c 
WHERE b.id = c.id

PHP:

was much faster than doing a similar one with a join:

SELECT b.detail, c.other_detail 
from tableb b 
INNER(left or right) JOIN ON b.id=c.id

PHP:

and I guess this will also have such perf advantages etc.

I don't know Oracle etc. so good, but one can do an experiment to see which one does the best performance with a query analyzer/monitor that monitors the index searches etc.
then you can go to your tutor and tell him, this is the results and blabla and do some research from some official sites.

 

Yeah, I was just using quick reference as to show how it was setup. The syntax was nowhere near correct in that

Oracle is no different then MySQL, it is all standard SQL language So the queries run faster using the where... Nice!

 

I have never used it the way you illistrated. I always do joins with "on" then follow up with the WHERE clauses.

I like to visually see the WHERE to split up the code as some of my transact SQL scripts are over 800 lines of SQL.