The following operators can be used in the WHERE clause to filter data:
| Operator | Description | Example |
|---|---|---|
| = | Equal | WHERE last_name = 'Potter' |
| > | Greater than | WHERE price > 5 |
| < | Less than | WHERE price < 5 |
| >= | Greater than or equal | WHERE price >= 5 |
| <= | Less than or equal | WHERE price <= 5 |
| != or <> | Not equal | WHERE last_name != 'Potter' |
| BETWEEN | Between a certain range | WHERE date_added BETWEEN '01-JAN-19' AND '31-DEC-19' |
| LIKE | Search for a pattern (place a % to represent a wildcard) |
WHERE last_name LIKE 'Po%'For example return ‘Potter’, ‘Poe’, and ‘Pope’. |
| IN | In a specified list | WHERE last_name IN ('Potter','Weasley','Granger') |
| ANY or SOME | Check operator against a list (IN with conditional) | WHERE 10 >= ANY (12,50,24) |
Example 1
SELECT first_name , last_name FROM contact WHERE first_name = 'Harry' AND last_name = 'Potter';
Example 2
SELECT item_title , release_date , item_rating FROM item WHERE release_date BETWEEN '01-JAN-83' AND '31-JAN-83' AND item_rating != 'R';
Also, keep in mind that these can all be inverted with the NOT keyword.
WHERE NOT last_name = 'Potter' would be synonymous with WHERE last_name != 'Potter'.