PostgreSQL – WHERE
PostgreSQL WHERE clause is used to filter rows of a table based on the condition applied on the columns of the table.
The syntax of WHERE clause to be used along with SELECT FROM statement is as follows.
SELECT column1, column2, columnN
FROM table_name
WHERE search_condition;
where
- column1, column2, columnN are the columns you want in the result.
- table_name is the name of the table from which the rows are fetched.
- search_condition is an expression that specifies the condition [usually on the column values].
Basic Example of WHERE clause
Consider the following table. We will apply WHERE clause on this table and use the same for all subsequent examples.
Now we will apply WHERE clause and fetch only those rows where age is greater that 20. The search_condition
is age>20 and the complete sql query would be:
SELECT *
FROM students
WHERE age>20;
PostgreSQL WHERE with AND Clause
We will use AND operator in the search_condition
and filter the rows of the page.
We will fetch those rows whose age is greater than 20 and id is greater than 1.
SELECT *
FROM students
WHERE age>20 AND id>1;
PostgreSQL WHERE with OR Clause
We will use OR operator in the search_condition
and filter the rows of the page.
We will fetch those rows whose age is greater than 22 and id is greater than 2.
SELECT *
FROM students
WHERE age>20 OR id>1;
PostgreSQL WHERE with IN Clause
We will use IN operator in the search_condition
and filter the rows of the page.
We will fetch those rows whose age is in the set of (22, 23).
SELECT *
FROM students
WHERE age IN (22, 23);
PostgreSQL WHERE with NOT IN Clause
We will use NOT IN operator in the search_condition
and filter the rows of the page.
We will fetch those rows whose age is not in the set of (22, 23).
SELECT *
FROM students
WHERE age NOT IN (22, 23);
Conclusion
In this PostgreSQL Tutorial, we used WHERE clause to filter rows of PostgreSQL Table based on conditions applied on the column values.