Re: and / where

From: Ed Prochak <edprochak_at_gmail.com>
Date: 6 Dec 2006 10:32:42 -0800
Message-ID: <1165429962.617584.273140@l12g2000cwl.googlegroups.com>

BigLearner wrote:
> Hai everyone,
>
> Can someone tell me whats the difference using AND and WHERE in the
> ansi join.
>
> Its basically used to further filter the query, atleast thats what i
> suppose.
>
> Can someone comment ?
>
> I see they are used almost in similar situations everywhere. I don't
> follow this.
>
> BigLearner

Simplied view of the syntax with the main pieces

SELECT ...
FROM  ... ( TABLE list including JOINs happen here)
WHERE .. (predicate clause)

GROUP BY ...
HAVING ...
{UNION|INTERSECT|EXCEPT} ... (another SELECT) ORDER BY Keep in mind there are actually TWO syntax rules for SELECT. Both are ANSI, though only the more recent version is discussed here as ANSI.

In the older SELECT systax, join conditions were declared in the WHERE clause. There was no LEFT JOIN operator. This is still supported as what's called Entry level conformance to the SQL standard.

So using the JOIN syntax
SELECT *
FROM a JOIN b ON a.group=b.grpid
WHERE a.start = sysdate AND b.rank>12 ;

or using the WHERE syntax
SELECT *
FROM a, b
WHERE a.group=b.name
  AND a.start = sysdate AND b.rank>12 ;

If you are joining tables on more than one column, then you can have AND in the JOIN also

SELECT *
FROM a JOIN b ON a.group=b.grpid AND a.lastname=b.grpname WHERE a.start = sysdate AND b.rank>12 ;

It is like the style choice of writing a FOR loop or a WHILE loop.

It's all in the documentation.

   HTH,
  ed Received on Wed Dec 06 2006 - 12:32:42 CST