# WHERE Clause Operators Available | Operator | Description | | ----------------------------------------------------------------- | --------------------------------------------------- | | = | Equal | | > | Greater than | | < | Less than | | >= | Greater than or equal | | <= | Less than or equal | | <> or != | Not equal | | AND | Logical operator AND | | OR | Logical operator OR | | [IN](https://www.postgresqltutorial.com/postgresql-in/) | Return true if a value matches any value in a list | | [BETWEEN](https://www.postgresqltutorial.com/postgresql-between/) | Return true if a value is between a range of values | | [LIKE](https://www.postgresqltutorial.com/postgresql-like/) | Return true if a value matches a pattern | | [IS NULL](https://www.postgresqltutorial.com/postgresql-is-null/) | Return true if a value is NULL | | NOT | Negate the result of other operators | ### Where * Cannot use column alias with where clause ```sql SELECT select_list FROM table_name WHERE condition ORDER BY sort_expression ``` ### Where with OR ```sql select first_name, last_name, date_of_birth from actors where date_of_birth < '1990-01-01' or date_of_birth > '1980-01-01' LIMIT 10; first_name | last_name | date_of_birth ------------+-----------+--------------------- Malin | Akerman | 1978-05-12 Tim | Allen | 1953-06-13 Julie | Andrews | 1935-10-01 Ivana | Baquero | 1994-06-11 Lorraine | Bracco | 1954-10-02 Alice | Braga | 1983-04-15 Marlon | Brando | 1924-04-03 Adrien | Brody | 1973-04-14 Peter | Carlberg | 1950-12-08 Gemma | Chan | 1982-11-29 select first_name, last_name, date_of_birth from actors where date_of_birth < '1990-01-01' or date_of_birth > '1980-01-01' ORDER BY date_of_birth LIMIT 10; -- with order by clause first_name | last_name | date_of_birth ------------+-----------+--------------------- Clark | Gable | 1901-02-01 00:00:00 Scatman | Crothers | 1910-05-23 00:00:00 Vivien | Leigh | 1913-11-05 00:00:00 Alec | Guiness | 1914-04-02 00:00:00 Judy | Garland | 1922-06-10 00:00:00 Marlon | Brando | 1924-04-03 00:00:00 Dick | Van Dyke | 1925-12-13 00:00:00 Sihung | Lung | 1930-01-01 00:00:00 Ian | Holm | 1931-09-12 00:00:00 Rebecca | Pan | 1931-12-29 00:00:00 ``` ### Where with AND ```sql select * from directors where date_of_birth > '1970-01-01' AND nationality = 'British'; director_id | first_name | last_name | date_of_birth | nationality -------------+------------+-----------+---------------+------------- 4 | Richard | Ayoade | 1977-06-12 | British 18 | Martin | McDonagh | 1970-03-26 | British ``` ### Where with BETWEEN (NOT BETWEEN) ```sql select * from directors where date_of_birth between '1970-01-01' and '1990-01-01'; director_id | first_name | last_name | date_of_birth | nationality -------------+------------+--------------------------+---------------+------------- 2 | Paul | Anderson | 1970-06-26 | American 4 | Richard | Ayoade | 1977-06-12 | British 11 | Florian | Henckel von Donnersmarck | 1973-05-02 | German 18 | Martin | McDonagh | 1970-03-26 | British ``` ### Where with LIKE ```sql SELECT first_name, last_name FROM actors WHERE actors.last_name LIKE '%son' ORDER BY first_name; first_name | last_name ------------+------------- Jack | Nicholson Lina | Leandersson Luke | Wilson Mykelti | Williamson Owen | Wilson Patrick | Wilson Samuel | Jackson Woody | Harrelson ``` ### Where with IN and NOT IN ```sql select * from directors where nationality in ('American', 'Japenese'); director_id | first_name | last_name | date_of_birth | nationality -------------+------------+--------------------------+---------------+-------------- 1 | Tomas | Alfredson | 1965-04-01 | Swedish 2 | Paul | Anderson | 1970-06-26 | American 3 | Wes | Anderson | 1969-05-01 | American 4 | Richard | Ayoade | 1977-06-12 | British 5 | Luc | Besson | 1959-03-18 | French 6 | James | Cameron | 1954-08-16 | American 7 | Guillermo | del Toro | 1964-10-09 | Mexican select * from directors where nationality not in ('American', 'Japenese'); director_id | first_name | last_name | date_of_birth | nationality -------------+------------+--------------------------+---------------+-------------- 1 | Tomas | Alfredson | 1965-04-01 | Swedish 4 | Richard | Ayoade | 1977-06-12 | British 5 | Luc | Besson | 1959-03-18 | French 7 | Guillermo | del Toro | 1964-10-09 | Mexican 10 | Kinji | Fukasaku | 1930-07-03 | Japanese 11 | Florian | Henckel von Donnersmarck | 1973-05-02 | German 12 | Terry | Jones | 1942-02-01 | British ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://dev117uday.gitbook.io/databases/sql/tables/where.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.