ANSI-Style Joins & Old-Style Joins!

I came across an article which states the differences between
ANSI-style JOINs & old-style JOINs. This was one among them:
---
The ANSI-style JOIN supports query constructions which the old-style
JOIN syntax does not support.
---
What does the above mean especially the term "query constructions"?
Thanks,
ArpanOne example is that the old-style syntax only directly supports joins
based on equality. In the ANSI syntax we can write:
SELECT *
FROM A
LEFT OUTER JOIN B
ON A.col1 BETWEEN B.col2 AND B.col3
To do that without the OUTER JOIN operator would require an inner join
and a UNION.
David Portas
SQL Server MVP
--|||The *= syntax was equality only and a table could appear only once as
eitehr preserved or unpreserved. Google aroudn for one of my olds
postings on how the outer works|||On 30 Jul 2005 01:38:32 -0700, Arpan wrote:

>I came across an article which states the differences between
>ANSI-style JOINs & old-style JOINs. This was one among them:
>---
>The ANSI-style JOIN supports query constructions which the old-style
>JOIN syntax does not support.
>---
>What does the above mean especially the term "query constructions"?
>Thanks,
>Arpan
Hi Arpan,
In addition to the answers provided by David and Celko, here are two
more examples:
1. The ANSI-style permits you to write
SELECT something
FROM A
LEFT OUTER JOIN B
ON B.col1 = A.col1
AND B.col2 IS NULL
or
SELECT something
FROM A
LEFT OUTER JOIN B
ON B.col1 = A.col1
WHERE B.col2 IS NULL
And the results of these two are quite different. With the old-style
joins, they'd be the same:
SELECT something
FROM A, B
WHERE B.col1 =* A.col1
AND B.col2 IS NULL
And which of the two possible result sets you'd get depends on how the
programmers decided to implement this. From what I've heard, Oracle's
result set would be different from SQL Server's.
2. FULL OUTER JOIN. This is not available in old-style.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

ANSI SQL Join syntax - multiple tables

I'm used to using the old-style JOIN syntax and performing the joins in the WHERE clause. I'm trying to figure out the ANSI syntax though for doing them in the FROM clause, and am curious if someone can help me out.

Here's the query I want:

SELECT *
FROM tableA A , tableB B , tableC C
WHERE A.col1 = B.col1
AND B.col2 = C.col2

Here is my converted ANSI (SQL-92) syntax:
SELECT *
FROM (tableA A JOIN tableB B ON A.col1 = B.col1 ) JOIN tableC C ON B.col2 = C.col2

Question:
Is my ANSI version correct (equivalent to the first query)?
Is that the only way to do it? I don't really like having to string multiple joins out with parenthesis like that.

Thanks.Sure...I would say INNER JOIN though (no that it matters)

You do know about outer joins though...right?

SELECT *
FROM tableA A
JOIN tableB B
ON A.col1 = B.col1
JOIN tableC C
ON B.col2 = C.col2|||the parentheses are definitely necessary in microsoft access, and nowhere else that i know of

i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary

so if in doubt, leave them out, just remember to code them back in if you use microsoft access

rudy
http://r937.com/|||Originally posted by r937
the parentheses are definitely necessary in microsoft access, and nowhere else that i know of

i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary

so if in doubt, leave them out, just remember to code them back in if you use microsoft access

rudy
http://r937.com/

Since when?

I always take them out in Access...same rules apply..esp when you duild your own sql in access...never use the qbe grid...(well maybe to select the columns...after that, it's painful...plus you can't union unless you go to the sql window...|||since when? since access 97

i dunno about later versions, perhaps the parentheses on 3+ table joins are no longer required, but they definitely were in access 97, and i ain't upgrading because i see no reason to

rudy|||I'm gonna check it out right now...didn't think so..

We're talking like SELECT a INNER join B on a.id = b.id inner join c on b.id = c.id...

right?|||yes, three tables or more, access 97 requires that you parenthesize them two at a time

select foo, bar
from (
a inner join b on a.id = b.id
)
inner join c on b.id = c.id

rudy|||Yup spent waaaay to much time with 2k...I remeber in 6.5 I think it was that way...

but for joins only...not in the predicates...

AND, Access still throws in waaaaaaaay too many parens...

You can get away with

SELECT *
FROM ((Table1 a
INNER JOIN table2 b
ON a.id = b.id)
INNER JOIN table3 c
ON b.id = c.id)

my bad...