 |
|
| Computers Forum Index » Computer - Databases » question about specifying the JOIN conditions for a... |
|
Page 1 of 1 |
|
| Author |
Message |
| The Quiet Center... |
 Posted: Fri Oct 23, 2009 1:47 pm |
|
|
|
Guest
|
I personally like to specify all my joins in the FROM part of a SELECT
instead of using = and *= in the WHERE part.
To me, this makes sense because that is what a database does: it makes
all the joins between all the tables and then uses the WHERE clause to
filter the joined result set.
But I have two questions:
given tables A, B, C, and D, is it always possible to specify the
intended JOIN in the FROM clause? Or are there cases where the join
conditions MUST be specified in the WHERE clause?
I think Celko wrote a book which goes from the mathematical
foundations of RDBMS usage? I'm interested in Prolog (logic
programming) these days as well. So any book which relates logic
programming, databases, and set theory and then eventually gets down
into practical SQL, I would like to know about. |
|
|
| Back to top |
|
|
|
| Lennart... |
| Posted: Fri Oct 23, 2009 2:38 pm |
|
|
|
Guest
|
On Oct 23, 3:47 pm, The Quiet Center <thequietcen... at (no spam) gmail.com> wrote:
Quote: I personally like to specify all my joins in the FROM part of a SELECT
instead of using = and *= in the WHERE part.
To me, this makes sense because that is what a database does: it makes
all the joins between all the tables and then uses the WHERE clause to
filter the joined result set.
But I have two questions:
given tables A, B, C, and D, is it always possible to specify the
intended JOIN in the FROM clause? Or are there cases where the join
conditions MUST be specified in the WHERE clause?
I think Celko wrote a book which goes from the mathematical
foundations of RDBMS usage? I'm interested in Prolog (logic
programming) these days as well. So any book which relates logic
programming, databases, and set theory and then eventually gets down
into practical SQL, I would like to know about.
I liked the following book, not sure if it is exactly what you are
looking for though:
http://www.amazon.com/Applied-Mathematics-Database-Professionals-Experts/dp/1590597451
I read it as a normal book, but when googling for:
applied mathematics database professional
It looked as if it where free pdf ebook version to download, did not
investigate that though.
/Lennart |
|
|
| Back to top |
|
|
|
| toby... |
| Posted: Sat Oct 24, 2009 2:23 am |
|
|
|
Guest
|
On Oct 23, 9:47 am, The Quiet Center <thequietcen... at (no spam) gmail.com> wrote:
Quote: I personally like to specify all my joins in the FROM part of a SELECT
instead of using = and *= in the WHERE part.
To me, this makes sense because that is what a database does: it makes
all the joins between all the tables and then uses the WHERE clause to
filter the joined result set.
I agree that this is good style.
Quote:
But I have two questions:
given tables A, B, C, and D, is it always possible to specify the
intended JOIN in the FROM clause? Or are there cases where the join
conditions MUST be specified in the WHERE clause?
I can't think of any.
Quote:
I think Celko wrote a book which goes from the mathematical
foundations of RDBMS usage? I'm interested in Prolog (logic
programming) these days as well. So any book which relates logic
programming, databases, and set theory and then eventually gets down
into practical SQL, I would like to know about.
You could try C.J. Date, "Logic and Databases", which is among those
listed here:
http://www.dbdebunk.com/books.html |
|
|
| Back to top |
|
|
|
| Ed Prochak... |
| Posted: Sat Oct 24, 2009 5:08 am |
|
|
|
Guest
|
On Oct 23, 9:47 am, The Quiet Center <thequietcen... at (no spam) gmail.com> wrote:
Quote: I personally like to specify all my joins in the FROM part of a SELECT
instead of using = and *= in the WHERE part.
To me, this makes sense because that is what a database does: it makes
all the joins between all the tables and then uses the WHERE clause to
filter the joined result set.
But I have two questions:
given tables A, B, C, and D, is it always possible to specify the
intended JOIN in the FROM clause? Or are there cases where the join
conditions MUST be specified in the WHERE clause?
Does not really make any difference. By the time the DB is working on
the joins, the parse step has long been completed.
That said, I think there are cases where you have a final filtering
clause that logically has nothing to do with the join. That's what I
would put in the WHERE clause (if I was writing the newer ANSI style).
So just write it so you can read in a year from now. |
|
|
| Back to top |
|
|
|
| David Portas... |
| Posted: Mon Oct 26, 2009 3:02 am |
|
|
|
Guest
|
"The Quiet Center" <thequietcenter at (no spam) gmail.com> wrote in message
news:52ebc576-cfd3-4f03-adb1-ea1b891ee676 at (no spam) l31g2000vbp.googlegroups.com...
Quote:
given tables A, B, C, and D, is it always possible to specify the
intended JOIN in the FROM clause? Or are there cases where the join
conditions MUST be specified in the WHERE clause?
If your WHERE clause references the "outer" part of an outer join then
expressions are not always interchangeable between WHERE and ON. So:
SELECT *
FROM i LEFT OUTER JOIN j
ON i.col = j.col
WHERE j.foo = 1;
means something different from:
SELECT *
FROM i LEFT OUTER JOIN j
ON i.col = j.col AND j.foo = 1;
Also, some DBMSs (Microsoft SQL Server anyway) will limit the scope of table
references allowed in the ON clause. In the ON clause SQL Server doesn't
allow you to reference a table before it is referred to in a FROM clause
(read in top to bottom order). As far as I understand it, this limitation
isn't part of the SQL standard and certainly other DBMSs (Oracle) don't have
the same problem. No such restriction applies to the WHERE clause because
FROM cannot come after WHERE.
--
David Portas |
|
|
| Back to top |
|
|
|
| --CELKO--... |
| Posted: Fri Oct 30, 2009 12:24 am |
|
|
|
Guest
|
Quote: I think Celko wrote a book which goes from the mathematical foundations of RDBMS usage?
I touch on it a lot as to why SQL works as it does in my books, but
none of them are devoted to mathematical foundations.
Trivial case: SELECT from a single table has to have a WHERE clause
unless you want to have every row returned.
I like the WHERE clause over the infixed INNER JOIN syntax because the
infixed operators lock you into a binary mindset. It is the
difference between using + for addition and using Big-Sigma notation
for summation. The examples I give are to express the following with
infixed operators and see how many ways you can do it and how
difficult they are to maintain or optimize"
1) T1.a BETWEEN T2.b AND T3.c -- a 3-ary relationship!
2) T1.a IN (T2.a, T2.a, T2.a, .. , Tn.a) -- an n-art relationship
Example (2) can be easily optimized with a hash table or other search
structure, but it is a bitch for an optimizer to find when it is
expressed as a chain of OR-ed search conditions in ON clauses.
When I get my head above water, I will do an article on this in the
trade press. |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Thu Nov 26, 2009 3:36 pm
|
|