Unexpected result in a statement when using ALL

Question

Unexpected result in a statement when using ALL

Navigation

#1 by (1 votes)

0

I have created the following tables:

productstest

orders

The query is as follows:

SELECT nombre
FROM productostest
WHERE id = ALL (SELECT idProducto FROM pedidos WHERE cantidad > 0);

This query does not return any results. What is the reason? for all idProductos it is true that they have quantities greater than 0.

According to the documentation:

The word ALL , which should follow a comparison operator, means "returns TRUE if the comparison is true for all values in the column that returns the subquery ".

mysql sql

asked by Serux 09.06.2018 в 16:58

source

1 answer

How to make a responsive image with css and html Javascript function that does not work

score 1 · Accepted Answer

I think you're misreading the operation of ALL. In this case you are asking him to compare every id in the productstest table with all ProductID in the orders table and, if it is equal to all, return the record.

I understand that you are interested in returning the record if the id in productstest matches any of the ProductID returned by the subquery.

In that case, instead of the operator ALL, you have to use the ANY operator.

SELECT nombre
FROM productostest
WHERE id = ANY (SELECT idProducto FROM pedidos WHERE cantidad > 0);

MySQL also has the IN operator that also allows you to do the same:

SELECT nombre
FROM productostest
WHERE id IN (SELECT idProducto FROM pedidos WHERE cantidad > 0);