In SQL, the less than operator (<) compares two expressions and returns TRUE if the left operand has a value lower than the right operand; otherwise, the result is FALSE.
Example
Here’s an example to demonstrate.
SELECT * FROM city
WHERE Population < 600;Result:
+------+--------------------+---------------+-------------+--------------+ | ID | Name | CountryCode | District | Population | |------+--------------------+---------------+-------------+--------------| | 62 | The Valley | AIA | – | 595 | | 2316 | Bantam | CCK | Home Island | 503 | | 2317 | West Island | CCK | West Island | 167 | | 2728 | Yaren | NRU | – | 559 | | 2912 | Adamstown | PCN | – | 42 | | 3333 | Fakaofo | TKL | Fakaofo | 300 | | 3538 | Città del Vaticano | VAT | – | 455 | +------+--------------------+---------------+-------------+--------------+
This query returns all cities that have a population less than 600.
Exclusive
The less than operator doesn’t include the specified value in its evaluation.
For example, the following example doesn’t return Fakaofo, which has a population of 300:
SELECT * FROM city
WHERE Population < 300;Result:
+------+-------------+---------------+-------------+--------------+ | ID | Name | CountryCode | District | Population | |------+-------------+---------------+-------------+--------------| | 2317 | West Island | CCK | West Island | 167 | | 2912 | Adamstown | PCN | – | 42 | +------+-------------+---------------+-------------+--------------+
To include cities with a population of 300, we would need to increase our specified value:
SELECT * FROM city
WHERE Population < 301;Result:
+------+-------------+---------------+-------------+--------------+ | ID | Name | CountryCode | District | Population | |------+-------------+---------------+-------------+--------------| | 2317 | West Island | CCK | West Island | 167 | | 2912 | Adamstown | PCN | – | 42 | | 3333 | Fakaofo | TKL | Fakaofo | 300 | +------+-------------+---------------+-------------+--------------+
Dates
Here’s an example to demonstrate using the less than operator to compare date values.
SELECT PetName, DOB
FROM Pets
WHERE DOB < '2020-01-01';Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fetch | 2019-08-16 | | Scratch | 2018-10-01 | +-----------+------------+
Strings
You can also use the less than operator to compare string values. When comparing with a string value, use quotes around the string.
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 Ab. Basically, it returns all cities that start with letters lower than Ab.
Multiple Conditions
If you have multiple conditions, you can use multiple less than 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. It also 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 less than 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 | +-----------+------------+