I believe you're looking for the IN
keyword; e.g.,
SELECT ProductName
FROM dbo.Products
WHERE
ProductID IN
(
SELECT ProductID
FROM dbo.OrderDetails
WHERE Quantity >= 120
)
The =
comparison operator does single value comparisons, while the IN
comparison operator asks if the left value is in the set on the right. Check out MySQL 8.0 Operators.
Technically, a correlated sub-query references the outer table. In this case, your sub-query is standalone and uncorrelated (e.g., SELECT ProductID FROM dbo.OrderDetails WHERE Quantity >= 120
can be executed as a standalone query). An example of a correlated sub-query might be:
SELECT ProductName
FROM dbo.Products AS Outer
WHERE
ProductID IN
(
SELECT ProductID
FROM dbo.OrderDetails
WHERE
Quantity >= 120
AND Quantity <= Outer.Available
);
On a loosely related note, consider using JOIN
s in place of sub-queries whenever possible, as JOIN
s are generally more efficient; e.g.,
SELECT ProductName
FROM
dbo.Products p
JOIN dbo.OrderDetails od ON ( p.ProductID = od.ProductID )
WHERE
od.Quantity >= 120