Question 331643 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Understanding a weird SQL clause

While learning some SQL, I stumbled upon this seemingly incorrect clause:

SELECT item 
FROM inventory 
WHERE 20 => price < 500;

The query does not result what was intended and should look like this:

SELECT item 
FROM inventory 
WHERE price => 20 AND price < 500;

I know that there is a better way to solve this. What really interests me here is why is the first query showed not failing at all? It returns results, sure not the ones we want but I expected it to fail completely.

Can someone help me to understand what the first query does at all? I couldn’t find any useful information.

Tags:

2

Answers


  1. 0

    MySQL comparison operators return 0 or 1 (or null), and that return itself can be used in a comparison.

    20 >= price < 500

    is doing the comparison 20 >= price, then seeing if the 0 or 1 or null is < 500 (which it will be, unless price is null). So the overall expression will be either 1 (true) or null.

    Login or Signup to reply.
  2. 0

    Assuming this is a copying error and the first comparison operator is actually >=, the expression

    20 >= price < 500

    is equivalent to

    (20 >= price) < 500

    because of operator precedence. In MySQL, boolean values are the integers 1 (for TRUE) and 0 (for FALSE). So if 20 >= price is true, it’s equivalent to

    1 < 500

    If that part if false, it’s equivalent to

    0 < 500

    Either way, the combined condition will be true, so the WHERE condition will result in all rows being returned.

    There are some languages where you can write expressions like this; Python allows "chained comparisons", automatically treating them as combined with and. So one can write

    if 20 <= price < 500:

    to test if the price is between 20 and 500. SQL doesn’t have this, but it does have a built-in BETWEEN operator:

    WHERE price BETWEEN 20 AND 499

    (since BETWEEN is inclusive, you have to subtract to get the equivalent of < instead of <=).

    The original code is also nonsensical for another reason: If a number is less than 500, it will also be less than or equal to 20. So 20 >= price is redundant. Are you sure it wasn’t 20 <= price (I notice that you swapped the order of arguments to >= when you wrote the "corrected" version)?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search