join

join

Post by Bert Sach » Tue, 04 Feb 2003 23:52:42



hi

i've noticed that there are two versions of "joins".
first, you can use it by "where" clause such as:
select t1.c1, t2.c1 from table1 as t1, table2 as t2
where t1.c3 = t2.c3

and the other version by using "join" :
select t1.c1, t2.c1 from table1 as t1
inner join table2 as t2 on t1.c3 = t2.c3

but what is the difference? is there any msdn article?

thanks
   bert sachs

 
 
 

join

Post by Nery R. Gonzále » Wed, 05 Feb 2003 05:00:31


Bert,

I think the difference is the version of SQL that DB supports. Before
relational databases the "Where" clause was used, the relational DB appeared
and voil here comes the Join structure. And because of backward
compatibility the "Where" clause is still supported, though, in a relational
DB environment the Join structure is preferred.

That's of what I understand, if I'm mistaken please, somebody, could point
it out.

Nery R. Gonzlez



Quote:> hi

> i've noticed that there are two versions of "joins".
> first, you can use it by "where" clause such as:
> select t1.c1, t2.c1 from table1 as t1, table2 as t2
> where t1.c3 = t2.c3

> and the other version by using "join" :
> select t1.c1, t2.c1 from table1 as t1
> inner join table2 as t2 on t1.c3 = t2.c3

> but what is the difference? is there any msdn article?

> thanks
>    bert sachs


 
 
 

join

Post by Bert Sach » Wed, 05 Feb 2003 07:13:57


hi

thanks for reply. i have read that the difference is in
runtime. using joins by "where" clause would create the
cartesian product at first and then removes the columns
that aren't matched to the conditions.
by using the "join" keyword there schould be checked for
matches at first and then the columns will be merged.
thats what i have heard from different sources but some
claim this isn't correct. so, is there any msdn article or
something? i hope you can understand my bad english :D

thanks in advance

bert

Quote:>-----Original Message-----
>Bert,

>I think the difference is the version of SQL that DB
supports. Before
>relational databases the "Where" clause was used, the

relational DB appeared
Quote:>and voil here comes the Join structure. And because of
backward
>compatibility the "Where" clause is still supported,

though, in a relational
Quote:>DB environment the Join structure is preferred.

>That's of what I understand, if I'm mistaken please,

somebody, could point
>it out.

>Nery R. Gonzlez


mensaje

>> hi

>> i've noticed that there are two versions of "joins".
>> first, you can use it by "where" clause such as:
>> select t1.c1, t2.c1 from table1 as t1, table2 as t2
>> where t1.c3 = t2.c3

>> and the other version by using "join" :
>> select t1.c1, t2.c1 from table1 as t1
>> inner join table2 as t2 on t1.c3 = t2.c3

>> but what is the difference? is there any msdn article?

>> thanks
>>    bert sachs

>.

 
 
 

join

Post by Bill » Thu, 06 Feb 2003 02:37:05


I think Codd and Date (the folks at IBM that "invented" SQL) would not
concur with your answer. ;)
Relational databases used the WHERE clause in a variety of ways to "Join"
rowsets. When ANSI got into the picture they standardized on the JOIN syntax
used by most relational engines.
Which is "better"? Well, in Access/JET if you don't use a JOIN, the engine
does not optimize the query as well as when you do. In SQL Server (IIRC)*,
it does not make any difference--the SQL compiler resolves to the same query
plan. If you look at the query plan (Query Analyzer) you can see that the
plans are the same for both syntaxes.

*IIRC (If I recall correctly)
--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________



> Bert,

> I think the difference is the version of SQL that DB supports. Before
> relational databases the "Where" clause was used, the relational DB
appeared
> and voil here comes the Join structure. And because of backward
> compatibility the "Where" clause is still supported, though, in a
relational
> DB environment the Join structure is preferred.

> That's of what I understand, if I'm mistaken please, somebody, could point
> it out.

> Nery R. Gonzlez



> > hi

> > i've noticed that there are two versions of "joins".
> > first, you can use it by "where" clause such as:
> > select t1.c1, t2.c1 from table1 as t1, table2 as t2
> > where t1.c3 = t2.c3

> > and the other version by using "join" :
> > select t1.c1, t2.c1 from table1 as t1
> > inner join table2 as t2 on t1.c3 = t2.c3

> > but what is the difference? is there any msdn article?

> > thanks
> >    bert sachs

 
 
 

join

Post by Bill » Fri, 07 Feb 2003 04:13:42


I just checked with my SQL goddess (Kimberly Tripp). She says it DOES make a
difference. Use the JOIN syntax. For inner JOINs it does not make much
difference (if any) but for outer JOINs there are several situations where
the WHERE-clause style can cause extra work.

hth

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________



> I think Codd and Date (the folks at IBM that "invented" SQL) would not
> concur with your answer. ;)
> Relational databases used the WHERE clause in a variety of ways to "Join"
> rowsets. When ANSI got into the picture they standardized on the JOIN
syntax
> used by most relational engines.
> Which is "better"? Well, in Access/JET if you don't use a JOIN, the engine
> does not optimize the query as well as when you do. In SQL Server (IIRC)*,
> it does not make any difference--the SQL compiler resolves to the same
query
> plan. If you look at the query plan (Query Analyzer) you can see that the
> plans are the same for both syntaxes.

> *IIRC (If I recall correctly)
> --
> ____________________________________
> Bill Vaughn
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________



> > Bert,

> > I think the difference is the version of SQL that DB supports. Before
> > relational databases the "Where" clause was used, the relational DB
> appeared
> > and voil here comes the Join structure. And because of backward
> > compatibility the "Where" clause is still supported, though, in a
> relational
> > DB environment the Join structure is preferred.

> > That's of what I understand, if I'm mistaken please, somebody, could
point
> > it out.

> > Nery R. Gonzlez



> > > hi

> > > i've noticed that there are two versions of "joins".
> > > first, you can use it by "where" clause such as:
> > > select t1.c1, t2.c1 from table1 as t1, table2 as t2
> > > where t1.c3 = t2.c3

> > > and the other version by using "join" :
> > > select t1.c1, t2.c1 from table1 as t1
> > > inner join table2 as t2 on t1.c3 = t2.c3

> > > but what is the difference? is there any msdn article?

> > > thanks
> > >    bert sachs

 
 
 

join

Post by Bert Sach » Fri, 07 Feb 2003 06:11:56


hi,

thanks for reply. i will check it with the query analyzer.
but i've heard something like that from other peoples, too

bye
 bert

>-----Original Message-----
>I just checked with my SQL goddess (Kimberly Tripp). She
says it DOES make a
>difference. Use the JOIN syntax. For inner JOINs it does
not make much
>difference (if any) but for outer JOINs there are several
situations where
>the WHERE-clause style can cause extra work.

>hth

>--
>____________________________________
>Bill Vaughn
>www.betav.com
>Please reply only to the newsgroup so that others can
benefit.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>__________________________________




>> I think Codd and Date (the folks at IBM that "invented"
SQL) would not
>> concur with your answer. ;)
>> Relational databases used the WHERE clause in a variety
of ways to "Join"
>> rowsets. When ANSI got into the picture they

standardized on the JOIN
Quote:>syntax
>> used by most relational engines.
>> Which is "better"? Well, in Access/JET if you don't use
a JOIN, the engine
>> does not optimize the query as well as when you do. In
SQL Server (IIRC)*,
>> it does not make any difference--the SQL compiler

resolves to the same
Quote:>query
>> plan. If you look at the query plan (Query Analyzer)

you can see that the

- Show quoted text -

>> plans are the same for both syntaxes.

>> *IIRC (If I recall correctly)
>> --
>> ____________________________________
>> Bill Vaughn
>> www.betav.com
>> Please reply only to the newsgroup so that others can
benefit.
>> This posting is provided "AS IS" with no warranties,
and confers no
>rights.
>> __________________________________


message

>> > Bert,

>> > I think the difference is the version of SQL that DB
supports. Before
>> > relational databases the "Where" clause was used, the
relational DB
>> appeared
>> > and voil here comes the Join structure. And because
of backward
>> > compatibility the "Where" clause is still supported,
though, in a
>> relational
>> > DB environment the Join structure is preferred.

>> > That's of what I understand, if I'm mistaken please,
somebody, could
>point
>> > it out.

>> > Nery R. Gonzlez


mensaje

>> > > hi

>> > > i've noticed that there are two versions of "joins".
>> > > first, you can use it by "where" clause such as:
>> > > select t1.c1, t2.c1 from table1 as t1, table2 as t2
>> > > where t1.c3 = t2.c3

>> > > and the other version by using "join" :
>> > > select t1.c1, t2.c1 from table1 as t1
>> > > inner join table2 as t2 on t1.c3 = t2.c3

>> > > but what is the difference? is there any msdn
article?

>> > > thanks
>> > >    bert sachs

>.

 
 
 

1. Right Join / Hash Joins / Nested Joins

When I execute a query, with a RIGHT JOIN - clause (Select * from Table1
right join Table2 on... ), it returns the correct fields:all from Table2
and the matching fields from Table1.
When I execute the same query for two other tables, it only returns the
matching fields.

By analyzing the query in the Query-Analyzer, I found out, that the
wrong results were generated with a HASH-JOIN, while the correct results
were generated with NESTED-LOOPS-JOINS.

As I deleted most of the rows of the (left) table, the results became
correct.

2. How to access an MS SQL db

3. Syntactic difference JOIN ON JOIN ON and JOIN JOIN ON ON?

4. Do you understand the relational model ? Do OODBMS provide an answer ?

5. Left join sql Request (double mixing left join)

6. Programming an app that uses a secured Access db

7. Converting ANSI Joins to "readable" joins

8. How to trigger notification?

9. Problem with Outer join and Inner joins

10. Settle the debate please - to join or not to join

11. Help on converting old style JOIN to ANSI JOIN

12. Outer joins returns inner join result!

13. Inner Join, Out Join Operators precedence