Join Styles

Hi,
Are there any difference(in performance) between these two type of join:
SELECT Customers.CustomerID,orders.orderid FROM Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
&
select c.customerid,o.orderid from customers c,orders o where
c.customerid=o.customerid
I tried them many times while STATISTICS IO/TIME were set on and couldn't
find considerable difference.
Thanks,
Leila
Leila...
These are simply two different ANSI standards. One is older than the other
one. In general SQL Server will build the exact same execution plan for
either of them. The only difference between the two is when there are OUTER
JOINS. The newer method of using the JOIN keyword is preferred and is part
of the SQL 99 standard.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||I find the INNER JOIN syntax more readable, it also allows you to separate
join criteria from filter criteria.
Try as you might, for inner joins with simple join criteria, I don't think
you'll ever see performance differences, because internally the engine sees
them as equivalent. Now if you have complex join and filter criteria, you
might be able to see differences if the filter takes place in the join as
opposed to after.
http://www.aspfaq.com/
(Reverse address to reply.)
"Leila" <leilas@.hotpop.com> wrote in message
news:#48398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||Leila:
The ANSI style (INNER JOIN) is better because for complex queries, the
optimizer can more easily select a better execution plan than for the old
style query; you won't find differences in simple queries though.
You might as well stop cross-posting.
Regards,
Eric Garza
AMIGE
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||http://www.microsoft.com/sql/techinf...ent/july23.asp
AMB
"Leila" wrote:

> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>