Subject Re: [firebird-support] Re: does firebird always go fastest way?
Author Martijn Tonies
> ok, i figure the answer to my initial question is then no..i've read
> about 'join' before but i never got too familiar with it so i just
> stuck to composing my sql-queries of combinations of "select * from
> (select ...)"-statements because i thought join was only there for the
> sake of readability (which i don't really care about) but if it really
> makes my search faster then it seems i'd indeed better do some reading
> up :-)

Well, let me explain a bit more ...

In the ideal case of a DBMS, you don't have to tell it "how" to get your
data, but only "what". This is part of the whole idea behind a DBMS.

Between your application and the physical data (on disk), there's a whole
series of layers.

One of these, is called an "optimizer". What this should do, is turn your
query (whatever it is, JOINs, SELECT FROM ( SELECT FROM )) into
something that it thinks is the most efficient way of getting your data.

In theory, this works fine.

In practice, it's very hard to write an optimizer that's fool proof with
regard to what uses to get your data AND to make it fast.

So, in theory, both a well-written JOIN statement and yours should
be able to return the same data in pretty much the same time.

In practice, it's more common to write a query statement that's readable
and as short as possible in order to simply the optimizers job.

That being said, the SQL language has several constructs to do what
you want to get as a result. Read up on JOIN and get to understand
what it does, it's very useful.

Here's a quick online reference:

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
My thoughts:
Database development questions? Check the forum!