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.

On Oct 23, 3:47*pm, The Quiet Center <thequietcen... (AT) gmail (DOT) com> wrote:
I liked the following book, not sure if it is exactly what you are
looking for though:

http://www.amazon.com/Applied-Mathem.../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

On Oct 23, 9:47*am, The Quiet Center <thequietcen... (AT) gmail (DOT) com> wrote:
I agree that this is good style.

I can't think of any.

You could try C.J. Date, "Logic and Databases", which is among those
listed here:
http://www.dbdebunk.com/books.html

On Oct 23, 9:47*am, The Quiet Center <thequietcen... (AT) gmail (DOT) com> wrote:
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.

"The Quiet Center" <thequietcenter (AT) gmail (DOT) com> wrote

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

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.