What is the difference between WHERE and INNER JOIN ON
willard
I have two table:
Class:
Class cClass
-------------------
1 Sheet
2 Tiger
3 Rabbit
Student:
ID Class Name
---------------------
1 2 Mary
2 1 Tom
3 3 Tim
4 2 Bob
........
and two SQL
SQL1:
SELECT Student.ID, Class.cClass,Student.Name
FROM Class INNER JOIN Student ON Class.Class = Student.Class;
^^^^^^^^^^
SQL2:
SELECT Student.ID, Class.cClass,Student.name
FROM Class,Student WHERE Class.Class=Student.Class
^^^^^
but the two SQL get the same result like below
ID cClass Name
---------------------
1 tiger Mary
2 Sheet Tom
3 rabit Tim
4 tiger Bob
........
The result is the same, but
What is the difference between WHERE and INNER JOIN?
Wiroj R.
willard <wil...@mail.tccgeb.edu.tw> wrote in article
<32FA57...@mail.tccgeb.edu.tw>...
> SQL1:
> SELECT Student.ID, Class.cClass,Student.Name
> FROM Class INNER JOIN Student ON Class.Class = Student.Class;
> ^^^^^^^^^^
> SQL2:
> SELECT Student.ID, Class.cClass,Student.name
> FROM Class,Student WHERE Class.Class=Student.Class
> ^^^^^
> ........
> The result is the same, but
> What is the difference between WHERE and INNER JOIN?
>
No difference. INNTER JOIN is ANSI SQL92 standard. MSSQL6.5 says that
we cannot use both syntaxes on the same query. IMHO, I prefer INNER JOIN
because we can easily recognize which column is the common key.
Justin Gilbert
There are a variety of differences. MS handles the joines in the where
clause. However, using the INNER/LEFT/RIGHT Joins in the select statement
helps in the performance in complex joins. This is because SQL Server
organizes the joins prior to creating a subset of rows.
Hope this helps.
Justin
Wiroj R. <wir...@tfas.th.com> wrote in article
<01bc1a4a$f27b4c00$d002...@tfnet.tfas.th.com>...
Ben McEwan
In article <01bc1db6$447b0ea0$9f3d...@MyHost.MINNETONKA>, From "Justin
Gilbert" <jgil...@iclretail.com>, the following was written:
> There are a variety of differences. MS handles the joines in the where
> clause. However, using the INNER/LEFT/RIGHT Joins in the select
> statement helps in the performance in complex joins. This is because
> SQL Server organizes the joins prior to creating a subset of rows.
Can you provide an example? In the spirit of rigorous testing, I've been
trying to get ANSI/Old-Style joins to perform differently, but haven't
been able to do so. Are you saying that using ANSI syntax overrides the
optimizer with regard to specifying table join order? If so, that has
definitely not been my experience, and I'd love to see an example of
that.
--
02/19/97 13:52
Ben McEwan
Geist, LLC - http://www.metis.com/~geist
bmc...@metis.com.
Justin Gilbert
> There are a variety of differences. MS handles the joines in the where
> clause. However, using the INNER/LEFT/RIGHT Joins in the select
> statement helps in the performance in complex joins. This is because
> SQL Server organizes the joins prior to creating a subset of rows.
Ben McEwan <bmc...@metis.com> wrote in article
<09970119135219...@metis.com>...
:
: Can you provide an example? In the spirit of rigorous testing, I've been
: trying to get ANSI/Old-Style joins to perform differently, but haven't
: been able to do so. Are you saying that using ANSI syntax overrides the
: optimizer with regard to specifying table join order? If so, that has
: definitely not been my experience, and I'd love to see an example of
: that.
As I mentioned, in simple joins, the where clause works well in joining
table. The
problem comes when one creates complex joins.
Consider the following example.
The tables are laid out where
Retrieve the sum of price from table1 where related table t2.bus_dt is a
value
then find the related entry in table3 who must exist in related to
table4 then
group by t2.emp_id.
The lines indicate the relationships.
** Don't ask about the table layout, it's not mine. **
In order to accomplish this, one would have to write a series of selects
into
temporary tables and update values accordingly. Try doing this with a where
clause. So far, I have not been able to come up with a scenario that
returns
the same value.
TABLE T1 TABLE T2 TABLE T3 TABLE T4
TRAN_ID -------------------> TRAN_ID -------> TRAN_ID ------------------->
TRAN_ID
PRICE BUS_DT /----> TRAN_LN_NBR
TRAN_LN_NBR ---------------------------/ LINK_LN_NBR ----------->
TRAN_LN_NBR
EMP_ID
Select sum(T1.PRICE)
From
((TABLE1 T1 Inner Join TABLE3 T3
on T1.tran_id = T3.tran_id and
T1.Tran_ln_nbr = T1.Tran_ln_nbr) inner join
TABLE2 T2 on T1.Tran_ID = T2.Tran_ID)
Inner Join
TABLE4 T4
On T4.Tran_ID = T1.Tran_ID and
T4.Tran_Ln_Nbr = T3.LINK_LN_NBR
Where
Convert(VarChar(12), Bus_Dt ) = Convert(VarChar(12), @SearchDate )
Group by T2.Emp_ID
Let me know your thoughts.
Justin