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
>
>

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,
LeilaLeila...
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/techinfo/tips/development/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
>
>

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,
LeilaLeila...
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/techin...ment/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
>
>

join Problem

From these 2 tables:

tblOrder tblAdditionalSerialNumber

OrderID SerialNo OrderID SerialNo

101 11NP3 101 11NP4

102 33BM8 101 11NP8

103 VX897 102 34BM9

102 35BM7

102 35BM3

I need to Return:

OrderID SerialNo

101 11NP3

101 11NP4

101 11NP8

102 33BM8

102 39BM9

102 35BM7

102 35BM3

103 VX897

SELECT OrderID, SerialNO

FROM tblOrder

UNION ALL

SELECT OrderID, SerialNO

FROM tblAdditionalSerialNumber

|||Is there a way to do it without using UNION? That would require that a repeat a lot of code.

|||

Jay,

UNION is the 'standard' method to combine data from two tables.

However, this example code might give you an idea.

Code Snippet

SET NOCOUNT ON

DECLARE @.Order table
( OrderID int,
SerialNo varchar(20)
)

DECLARE @.AdditionalSerialNumber table
( OrderID int,
SerialNo varchar(20)
)

INSERT INTO @.Order VALUES ( 101, '11NP3' )
INSERT INTO @.Order VALUES ( 102, '33BM8' )
INSERT INTO @.Order VALUES ( 103, 'VX897' )

INSERT INTO @.AdditionalSerialNumber VALUES ( 101, '11NP4' )
INSERT INTO @.AdditionalSerialNumber VALUES ( 101, '11NP8' )
INSERT INTO @.AdditionalSerialNumber VALUES ( 102, '34BM9' )
INSERT INTO @.AdditionalSerialNumber VALUES ( 102, '35BM7' )
INSERT INTO @.AdditionalSerialNumber VALUES ( 102, '35BM3' )

SELECT DISTINCT
OrderID = coalesce( o.OrderID, a.OrderID ),
SerialNo = coalesce( o.SerialNo, a.SerialNo )
FROM @.Order o
FULL JOIN @.AdditionalSerialNumber a
ON 1 <> 1
ORDER BY OrderID

OrderID SerialNo
-- --
101 11NP3
101 11NP4
101 11NP8
102 33BM8
102 34BM9
102 35BM3
102 35BM7
103 VX897

As a footnote, using 'tbl' as a table prefix is quite 'out of date' since context will always tell you the object is a table. consider that you are wasting keystrokes that provide no value.

|||

Jay,

your example given is a good example of when you have something split into two tables, when it should be just a single table. In such cases, when you want to treat the two as one, 'UNION' is the tool to use.

/Kenneth

|||

You have to use the UNION or UNION ALL to perform this. Arnie sample quire is tricky alternative; but it is really work perfetly with 2 tables..

|||

I always knew I could do it with UNION ALL. I wanted to see if I could avoid repeating the entire query which is much more complicated than the simple example. Anyhow, I decided to go ahead and do it that way.

Thanks guys. Happy coding.