In SQL, the greater than operator (>) compares two expressions and returns TRUE if the left operand has a value higher than the right operand; otherwise, it returns FALSE.
Example
Here’s an example to demonstrate.
SELECT * FROM city
WHERE Population > 9000000;Result:
+------+-----------------+---------------+--------------+--------------+ | ID | Name | CountryCode | District | Population | |------+-----------------+---------------+--------------+--------------| | 206 | São Paulo | BRA | São Paulo | 9968485 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 2822 | Karachi | PAK | Sindh | 9269265 | +------+-----------------+---------------+--------------+--------------+
This query returns all cities that have a population of greater than 9 million.
Exclusive
The greater than operator doesn’t include the specified value in its evaluation.
For example, the following example doesn’t return Karachi, which, according to this database, has a population of exactly 9269265:
SELECT * FROM city
WHERE Population > 9269265;Result:
+------+-----------------+---------------+--------------+--------------+ | ID | Name | CountryCode | District | Population | |------+-----------------+---------------+--------------+--------------| | 206 | São Paulo | BRA | São Paulo | 9968485 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 2331 | Seoul | KOR | Seoul | 9981619 | +------+-----------------+---------------+--------------+--------------+
To include cities with a population of 9269265, we would need to decrease our specified value:
SELECT * FROM city
WHERE Population > 9269264;Result:
+------+-----------------+---------------+--------------+--------------+ | ID | Name | CountryCode | District | Population | |------+-----------------+---------------+--------------+--------------| | 206 | São Paulo | BRA | São Paulo | 9968485 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 2822 | Karachi | PAK | Sindh | 9269265 | +------+-----------------+---------------+--------------+--------------+
Either that, or we could use the greater than or equal to (>=) operator.
SELECT * FROM city
WHERE Population >= 9269265;Result:
+------+-----------------+---------------+--------------+--------------+ | ID | Name | CountryCode | District | Population | |------+-----------------+---------------+--------------+--------------| | 206 | São Paulo | BRA | São Paulo | 9968485 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 2822 | Karachi | PAK | Sindh | 9269265 | +------+-----------------+---------------+--------------+--------------+
Dates
Here’s an example to demonstrate using the greater than operator to compare date values.
SELECT PetName, DOB
FROM Pets
WHERE DOB > '2020-01-01';Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fluffy | 2020-11-20 | | Wag | 2020-03-15 | | Tweet | 2020-11-28 | | Fluffy | 2020-09-17 | +-----------+------------+
Strings
You can also use the greater than operator to compare string values. When comparing with a string value, use quotes around the string.
SELECT * FROM city
WHERE Name > 'Zukovski'
ORDER BY Name;Result:
+------+----------+---------------+------------+--------------+ | ID | Name | CountryCode | District | Population | |------+----------+---------------+------------+--------------| | 2669 | Zumpango | MEX | México | 99781 | | 2025 | Zunyi | CHN | Guizhou | 261862 | | 3245 | Zürich | CHE | Zürich | 336800 | | 3145 | Zwickau | DEU | Saksi | 104146 | | 28 | Zwolle | NLD | Overijssel | 105819 | | 3446 | Zytomyr | UKR | Zytomyr | 297000 | +------+----------+---------------+------------+--------------+
Multiple Conditions
If you have multiple conditions, you can use multiple greater than operators.
Like this:
SELECT * FROM city
WHERE Name > 'Zukovski' AND Population > 200000;Result:
+------+---------+---------------+------------+--------------+ | ID | Name | CountryCode | District | Population | |------+---------+---------------+------------+--------------| | 2025 | Zunyi | CHN | Guizhou | 261862 | | 3245 | Zürich | CHE | Zürich | 336800 | | 3446 | Zytomyr | UKR | Zytomyr | 297000 | +------+---------+---------------+------------+--------------+
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 > 'Zukovski'
AND Population > 100000
OR District = 'Overijssel';Result:
+------+----------+---------------+------------+--------------+ | ID | Name | CountryCode | District | Population | |------+----------+---------------+------------+--------------| | 15 | Enschede | NLD | Overijssel | 149544 | | 28 | Zwolle | NLD | Overijssel | 105819 | | 2025 | Zunyi | CHN | Guizhou | 261862 | | 3145 | Zwickau | DEU | Saksi | 104146 | | 3245 | Zürich | CHE | Zürich | 336800 | | 3446 | Zytomyr | UKR | Zytomyr | 297000 | +------+----------+---------------+------------+--------------+
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 > 'Zukovski' AND Population > 100000 or District = 'Overijssel'. We can tell just by looking at this that all cities from the Overijssel district will be returned, plus any cities that satisfy the first criteria.
This is like doing the following:
SELECT * FROM city
WHERE (Name > 'Zukovski'
AND Population > 100000)
OR District = 'Overijssel';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 > 'Zukovski'
AND (Population > 100000
OR District = 'Overijssel');Result:
+------+---------+---------------+------------+--------------+ | ID | Name | CountryCode | District | Population | |------+---------+---------------+------------+--------------| | 28 | Zwolle | NLD | Overijssel | 105819 | | 2025 | Zunyi | CHN | Guizhou | 261862 | | 3145 | Zwickau | DEU | Saksi | 104146 | | 3245 | Zürich | CHE | Zürich | 336800 | | 3446 | Zytomyr | UKR | Zytomyr | 297000 | +------+---------+---------------+------------+--------------+
This time we got only those cities that satisfied both Population > 100000 OR District = 'Overijssel' and Name > 'Overijssel'.
This resulted in only one city from Overijssel being returned, as opposed to two in the previous query.
Negating the Condition
You can use the NOT operator to negate the condition provided by the greater than operator. Here’s an example:
SELECT PetName, DOB
FROM Pets
WHERE NOT DOB > '2019-12-31';Result:
+-----------+------------+ | PetName | DOB | |-----------+------------| | Fetch | 2019-08-16 | | Scratch | 2018-10-01 | +-----------+------------+