subquery difference confusion

Question

In this code we're asking for minimal price for product of particular supplier but I' don't understand why we have to set an alias for the outer query.

QUERY 1: - Returns any records where UnitPrice is the smallest.

SELECT SupplierID
    ,ProductName
    ,UnitPrice
FROM Products
WHERE UnitPrice = (
        SELECT MIN(UnitPrice)
        FROM Products
        WHERE SupplierID = Products.SupplierID
        )

QUERY 2: - Returns records with the lowest price for each supplier.

SELECT SupplierID
    ,ProductName
    ,UnitPrice
FROM Products AS p1
WHERE UnitPrice = (
        SELECT MIN(UnitPrice)
        FROM Products
        WHERE SupplierID = p1.SupplierID
        )

Is this code recursive ?

I'm using the Northwind sample database.

Can you at least give us your schema? Without your schema it's a bit difficult to guess why the query is not working. A working sqlfiddle link might help, too. — Jochem Kuijpers, Mar 10 '18 at 22:06
Your first query does not reference supplier_id from the outer query and is probably not what you want - hence the alias in the second query which forces a test with the supplier_id from the outer query. Search for correlated sub queries for more detail. — P.Salmon, Mar 11 '18 at 09:26

score 1 · Answer 1 · answered Mar 10 '18 at 22:38

In first one, supplierID in sub query is referring to sub query's Products table, which has no reference to outer table. So it is executed once to get min unit price among all supplier IDs. This is basically a nested sub query.

In second one, sub query is referring to outer query using p1.supplierID, so the sub query will be executed for each supplierID in outer query. It will get min unit price for each supplier. This is a correlated sub query.

More info on subqueries

score 1 · Accepted Answer · answered Mar 10 '18 at 23:07

QUERY 1: - Returns any records where UnitPrice is the smallest of all UnitPrice's

SELECT SupplierID
    ,ProductName
    ,UnitPrice
FROM Products
WHERE UnitPrice = (
        SELECT MIN(UnitPrice)
        FROM Products
        WHERE SupplierID = Products.SupplierID
        )

You could completely remove the WHERE SupplierID = Products.SupplierID from the subquery because it has no impact on your final results.

Think of it this way (Return all records where the UnitPrice = 1):

SELECT SupplierID
    ,ProductName
    ,UnitPrice
FROM Products
WHERE UnitPrice = ('1')

QUERY 2: - Returns records with the lowest price for each supplier.

SELECT SupplierID
    ,ProductName
    ,UnitPrice
FROM Products AS p1
WHERE UnitPrice = (
        SELECT MIN(UnitPrice)
        FROM Products
        WHERE SupplierID = p1.SupplierID
        )

Think of it this way, "for each p1.SupplierID (this is every supplier in the Products table), return any records where the UnitPrice is the lowest this particular supplier has to offer"

[DEMO HERE WITH SAMPLE DATA]

subquery difference confusion

2 Answers2