SQL code works as a subquery, but not a query. WHY?

Question

OK, so SQL evaluates subqueries from the inside out right? So it seems logical to infer from this that if a SELECT statement is allowed as a subquery, it should also function on its own as a single, solitary query. I now know that isn't the case, but, for the love, why? Example:

This works:

SELECT cust_name, cust_state,
(SELECT COUNT(*) FROM orders
WHERE orders.cust_id = Customers.cust_id) AS innerquery
FROM customers
ORDER BY cust_name;

But this doesn't:

SELECT COUNT(*) FROM orders
WHERE orders.cust_id = Customers.cust_id;

It gives the error: "The multi-part identifier "Customers.cust_id" could not be bound."

I get that this is basically using a JOIN where no JOIN is needed, but if that's the case, why does it work in the subquery?

Thanks for your help. I'm using MS SQL Server Express 2008R2. The tables are from the Sams DB from the Sam's 10 minute SQL book.

Answer 1

Whilst your subquery is working from the inside out you have to be aware that your subquery WHERE is accessing a variable from the outer query.

This means that both the outer and the subquery are linked and cannot therefore be separated.

If the subquery does not make reference to the outer query then it can be run on its own.



Have fun.

Answer 2

A subquery can see the outer query but the outer query can only see what the subquery passes back. In this case it should be obvious that the subquery is not itself looking at the customer table, so it can't have a WHERE condition that includes Customer.cust_id. To do that you would need a join such as...



SELECT COUNT(*)

FROM orders, customers

WHERE orders.cust_id = customers.cust_id;

Answer 3

Of course it doesn't execute. How is it supposed to know what 'customers.cust_id' is? You haven't defined in your query what 'customers' is.



In the inner query, 'customers' is simply a place holder, it is not a direct reference to the customers table.



Let's say you changed your query to look like so...

SELECT cust_name, cust_state,

(SELECT COUNT(*) FROM orders

WHERE orders.cust_id = users.cust_id) AS innerquery

FROM customers as users

ORDER BY cust_name;



and then try to run the inner select by itself...

SELECT COUNT(*) FROM orders

WHERE orders.cust_id = users.cust_id



What does 'users' represent? The query doesn't know, and realistically, neither do you because there is no definition of 'users' anywhere in this query.



In your complete query, 'customers' works because you have defined it, albeit indirectly, in the FROM clause. If you break it down, the query is actually saying "FROM customers AS customers". At this point, your inner query can see it.

Answer 4

hi, this could be a topic which you do no longer specify the connect form wether top, left or inner in such case you will possibly need top ot left form of connect you may define it as : decide on PageCategory.PageCategoryName, PageCategory.PageCategoryID FROM web site LEFT OUTER connect PageCategory ON web site.PageCategoryID = PageCategory.PageCategoryID i won't describe extra on a respond ... sorry, yet i wish that's the difficulty