> For the complete documentation index, see [llms.txt](https://dev117uday.gitbook.io/databases/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dev117uday.gitbook.io/databases/sql/tables/where.md). # 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 This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## 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, and the optional `goal` query parameter: ``` GET https://dev117uday.gitbook.io/databases/sql/tables/where.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. 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.