CODEWITHSUNDEEP
sqlsql-server
QWERTY1234567890
QWERTY1234567890

Reputation: 109

subquery difference confusion

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.

Upvotes: 0

Views: 117

Answers (2)

Brien Foss
Brien Foss

Reputation: 3367

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]

Upvotes: 1

Ajay
Ajay

Reputation: 594

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

Upvotes: 1

Related Questions