In SQL, the less than or equal to operator (<=) compares two expressions and returns TRUE if the left operand has a value lower than or equal to the right operand; otherwise, the result is FALSE.
You can use it to determine whether a value is less than or equal to another value.
Example
Here’s an example to demonstrate.
SELECT * FROM city
WHERE Population <= 455;Result:
+------+--------------------+---------------+-------------+--------------+ | ID | Name | CountryCode | District | Population | |------+--------------------+---------------+-------------+--------------| | 2317 | West Island | CCK | West Island | 167 | | 2912 | Adamstown | PCN | – | 42 | | 3333 | Fakaofo | TKL | Fakaofo | 300 | | 3538 | Città del Vaticano | VAT | – | 455 | +------+--------------------+---------------+-------------+--------------+
This query returns all cities that have a population of 455 or lower.
Inclusive
The less than or equal to operator includes the specified value in its evaluation. We saw this in the previous example when it included the city with a population of 455, which was the exact value that we specified.
If we didn’t want this city to be included, we would need to either lower the value:
SELECT * FROM city
WHERE Population <= 454;Result:
+------+-------------+---------------+-------------+--------------+ | ID | Name | CountryCode | District | Population | |------+-------------+---------------+-------------+--------------| | 2317 | West Island | CCK | West Island | 167 | | 2912 | Adamstown | PCN | – | 42 | | 3333 | Fakaofo | TKL | Fakaofo | 300 | +------+-------------+---------------+-------------+--------------+
Or we could simply use the less than operator (<) instead:
SELECT * FROM city
WHERE Population < 455;Result:
+------+-------------+---------------+-------------+--------------+ | ID | Name | CountryCode | District | Population | |------+-------------+---------------+-------------+--------------| | 2317 | West Island | CCK | West Island | 167 | | 2912 | Adamstown | PCN | – | 42 | | 3333 | Fakaofo | TKL | Fakaofo | 300 | +------+-------------+---------------+-------------+--------------+
The less than operator doesn’t include the value that you specify.
Dates
Here’s an example to demonstrate using the less than or equal to operator to compare date values.
SELECT PetName, DOB
FROM Pets
WHERE DOB <= '2020-03-15';Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fetch | 2019-08-16 | | Scratch | 2018-10-01 | | Wag | 2020-03-15 | +-----------+------------+
Strings
You can also use the <= operator to compare string values. When comparing with a string value, use quotes around the string literal.
SELECT * FROM city
WHERE Name <= 'Ab';Result:
+------+------------------------------+---------------+---------------------+--------------+ | ID | Name | CountryCode | District | Population | |------+------------------------------+---------------+---------------------+--------------| | 20 | ´s-Hertogenbosch | NLD | Noord-Brabant | 129170 | | 548 | Šumen | BGR | Varna | 94686 | | 670 | A Coruña (La Coruña) | ESP | Galicia | 243402 | | 698 | [San Cristóbal de] la Laguna | ESP | Canary Islands | 127945 | | 2450 | Šiauliai | LTU | Šiauliai | 146563 | | 3097 | Aachen | DEU | Nordrhein-Westfalen | 243825 | | 3318 | Aalborg | DNK | Nordjylland | 161161 | | 3479 | Šostka | UKR | Sumy | 90000 | | 3665 | Šahty | RUS | Rostov-na-Donu | 221800 | | 3736 | Štšolkovo | RUS | Moskova | 104900 | +------+------------------------------+---------------+---------------------+--------------+
This query returns all cities that are less than or equal to Ab. Basically, it returns all cities that start with letters lower than Ab or exactly Ab.
Multiple Conditions
If you have multiple conditions, you can use multiple <= operators.
Like this:
SELECT * FROM city
WHERE Name <= 'Ab' AND Population <= 100000;Result:
+------+--------+---------------+------------+--------------+ | ID | Name | CountryCode | District | Population | |------+--------+---------------+------------+--------------| | 548 | Šumen | BGR | Varna | 94686 | | 3479 | Šostka | UKR | Sumy | 90000 | +------+--------+---------------+------------+--------------+
Precedence
You can also use a combination of operators when filtering the results.
Note that SQL has an order of precedence that it assigns to different operator types. For example, it evaluates any conditional operators before any logical operators, such as AND and OR. Furthermore, it evaluates any AND operators before any OR operators.
Parentheses have a higher precedence than all operators, and so you can use parentheses to specify the order in which each condition should be evaluated.
Consider the following example:
SELECT * FROM city
WHERE Name <= 'Ab'
AND Population <= 100000
OR District = 'Canary Islands';Result:
+------+------------------------------+---------------+----------------+--------------+ | ID | Name | CountryCode | District | Population | |------+------------------------------+---------------+----------------+--------------| | 548 | Šumen | BGR | Varna | 94686 | | 660 | Las Palmas de Gran Canaria | ESP | Canary Islands | 354757 | | 672 | Santa Cruz de Tenerife | ESP | Canary Islands | 213050 | | 698 | [San Cristóbal de] la Laguna | ESP | Canary Islands | 127945 | | 3479 | Šostka | UKR | Sumy | 90000 | +------+------------------------------+---------------+----------------+--------------+
In this query, I didn’t provide any parentheses, and so the AND operator was evaluated before the OR operator.
Therefore we got rows that satisfied either Name <= 'Ab' AND Population <= 100000 or District = 'Canary Islands'. We can tell just by looking at this that all cities from the Canary Islands will be returned, plus any cities that satisfy the first criteria.
This is like doing the following:
SELECT * FROM city
WHERE (Name <= 'Ab'
AND Population <= 100000)
OR District = 'Canary Islands';That would give us the same result as the previous query without parentheses.
But look what happens when we move the parentheses to the OR condition.
SELECT * FROM city
WHERE Name <= 'Ab'
AND (Population <= 100000
OR District = 'Canary Islands');Result:
+------+------------------------------+---------------+----------------+--------------+ | ID | Name | CountryCode | District | Population | |------+------------------------------+---------------+----------------+--------------| | 548 | Šumen | BGR | Varna | 94686 | | 698 | [San Cristóbal de] la Laguna | ESP | Canary Islands | 127945 | | 3479 | Šostka | UKR | Sumy | 90000 | +------+------------------------------+---------------+----------------+--------------+
This time we got only those cities that satisfied both Population <= 100000 OR District = 'Canary Islands' and Name <= 'Ab'.
Negating the Condition
You can use the NOT operator to negate the condition provided by the <= operator. Here’s an example:
SELECT PetName, DOB
FROM Pets
WHERE NOT DOB <= '2019-12-31';Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fluffy | 2020-11-20 | | Wag | 2020-03-15 | | Tweet | 2020-11-28 | | Fluffy | 2020-09-17 | +-----------+------------+