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.

</>
Copy
 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.

PostgreSQL Add Column with Default Value

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:

</>
Copy
SELECT *
	FROM students
	WHERE age>20;
PostgreSQL WHERE

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.

</>
Copy
SELECT *
	FROM students
	WHERE age>20 AND id>1;
PostgreSQL WHERE with AND operator

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.

</>
Copy
SELECT *
	FROM students
	WHERE age>20 OR id>1;
PostgreSQL WHERE with OR operator

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).

</>
Copy
SELECT *
	FROM students
	WHERE age IN (22, 23);
PostgreSQL WHERE with IN operator

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).

</>
Copy
SELECT *
	FROM students
	WHERE age NOT IN (22, 23);
PostgreSQL WHERE with NOT IN operator

Conclusion

In this PostgreSQL Tutorial, we used WHERE clause to filter rows of PostgreSQL Table based on conditions applied on the column values.