CODEWITHSUNDEEP
mysqlsql
Lr Ayman
Lr Ayman

Reputation: 17

column is unknown when the column and the table are defined?

i stumbled upon this example :

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID );

it works with no problem and shows the requested rows, but when i tried to run the subquery as a query like this

SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID ;

it tells me that Suppliers.supplierID is an unknown column, what am i missing ?

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You think the query is:

SELECT s.SupplierName
FROM Suppliers s
WHERE EXISTS (SELECT p.ProductName
              FROM Products p
              WHERE p.SupplierID = s.supplierID
             );

But if p.ProductName does not exist, then SQL reaches out to the outer query and it is interpreted as:

SELECT s.SupplierName
FROM Suppliers s
WHERE EXISTS (SELECT s.ProductName
              FROM Products p
              WHERE p.SupplierID = s.supplierID
             );

In this case, it makes no difference whatsoever, because EXISTS only checks for the existence of rows, not columns. I usually write this using SELECT 1.

That said, it can make a difference in other contexts. Hence, the moral still applies:

Moral: Always qualify all column references, especially in a query that has more than one table reference.

Upvotes: 1

Related Questions