Subquery automatically producing cross join

I am not certain WHY but when I follow the example (from the northwind datase in a ms sql server) to do a subquery on Microsoft SQL Server Management Studio 2008 by typing in the code like shown below,

Select Orders.OrderID, 
       (Select Customers.CompanyName 
          From Customers 
          Where Customers.CustomerID = Orders.CustomerID) As Company Name
  From Orders, 
       Customers

This sql code with subquery automatically gained a cross join and become

    Select Orders.OrderID,
           (Select Customers.CompanyName 
              From Customers
             Where Customers.CustomerID = Orders.CustomerID) As Company Name
      From Orders 
CROSS JOIN Customers as Customers_1

I have played around with several variation of this but with no luck in eliminating this problem. Is this a known bug for microsoft sql server management studio 20开发者_如何学运维08? If so, has it been patched, how do I find the patched? Otherwise, how can I report this to Microsoft and get them to really fixed it quickly?

In my actual query, I need to query/lookup the name of this particular table about 50 times by equating the ID and I think it is simply dumb having to do a JOIN of any sort for this because the code is crumpy, VERY long, and performance may be poor?

---

The subquery isn't causing the cross join, the lack of a condition controlling the join is. You need something like this:

Select Orders.OrderID, (Select Customers.CompanyName From Customers Where Customers.CustomerID = Orders.CustomerID) As Company Name 
From Orders, Customers
Where Orders.CustomerID = Customers.CustomerID

---

I don't know why a sub-query is suggested by your book -- I would do it like this:

Select Orders.OrderID, Customers.CompanyName 
  From Orders 
  left join Customers on Customers.CustomerID = Orders.CustomerID

---

Looks like it should be a correlated-subquery

Select Orders.OrderID,

   (Select Customers.CompanyName 
      From Customers 
      Where **Customers.CustomerID = Orders.CustomerID**) As Company Name

From Orders

--, -- Customers

Why would you need Customers again when the inner Correlated Subquery brings the customer Name for each Order that is processed?

The Management Studio's insistence on adding CROSS JOIN is a warning that you are doing something strange. Trying to query two tables: Customer,Orders without any join condition.

Also, the query optimizer will usually convert these correlated sub-queries into joins during processing, but you can use the clearer syntax where appropriate.

Where is it appropriate? Particularly if you need to generate some sort of aggregate on the inner query.