In PostgreSQL, the WHERE clause is used to filter the results of different statements such as SELECT, DELETE, UPDATE, etc. In PostgreSQL, the WHERE clause specifies a condition for retrieving the table's record. Within the specified condition, different operators can be used, such as AND, OR, etc. These operators determine the filtration criteria of the WHERE clause.

In this post, you will learn different use cases of the WHERE clause within the SELECT statement.

How to Use WHERE clause in PostgreSQL?

In Postgres, the WHERE clause takes a condition and checks it. If the specified condition is true, then the WHERE clause will return a specific value or set of values from the targeted table.

Following will be the basic syntax of the WHERE clause:

SELECT col_1, col_2, ..... col_N  
FROM tab_name
WHERE [condition]

Let’s understand the syntax of the WHERE clause step-by-step:

● SELECT command will select a single column or a list of columns.

● FROM clause specifies a table or list of tables to be targeted.

● tab_name is a table to be selected.

● WHERE clause takes a condition and checks if it's true or not. It comes after the FROM clause of any statement.

● condition represents a single Boolean expression or a combination of several expressions.

Example # 1: Select the Players Whose Age is Greater Than 25

Follow the below-given steps to understand the working of the WHERE clause in PostgreSQL:

Step 1: Describe the Targeted Table Using Select Command

Let’s execute the SELECT command followed by an asterisk to retrieve all the columns of the “team_member” table:

SELECT * FROM team_members;

The above snippet shows that the team_members table has eight players. Some players are above 25, and others are under 25 years.

Step 2: Filter Players Above 25 Using WHERE Clause

Execute the SELECT query with the collaboration of the WHERE clause to fetch the name of only those players whose age is above 25:

SELECT
  player_name, player_age
FROM
  team_members
WHERE
  player_age > 25;

Here, we utilized the greater than sign in the WHERE clause to fetch the record of only those team_members whose age is greater than 25:

The output verifies that the WHERE clause returns the filtered data.

Example # 2: Select the Players Whose Age is Equal to 22 or Whose Name is John

We will use the OR operator in the WHERE clause to fetch the player's name whose age is equal to 22 or whose name is John:

SELECT * FROM team_members
WHERE player_age = 22 OR player_name = 'John';

The output clarifies that the WHERE clause retrieves the filtered data.

Example # 3: Select the Players Whose Name is “John” and Whose Age is Greater than 25

In PostgreSQL, the AND operator returns true if both the conditions satisfy the specified criteria. In this example, we will use the ANDoperator, which will return the data of only those players who satisfy both conditions:

SELECT * FROM team_members
WHERE player_name = 'John' AND player_age > 25;

This is how you can use the AND operator in the WHERE clause.

Example # 4: Select the Players Whose Name Starts With ‘J’

In PostgreSQL, the LIKE operator is used for pattern matching. In this example, we will use the LIKE operator with the WHERE clause to filter the players whose name starts with the “J”:

SELECT * FROM team_members
WHERE player_name LIKE 'J%';

As shown in the output, the WHERE clause with the aid of the LIKE operator fetched only those players whose name starts with the “J”.

Example # 5: Select the Players Whose Name is Not Equal to John

In PostgreSQL, “<>” and “!=” symbols are used to check the non-equal values:

SELECT * FROM team_members
WHERE player_name != 'John';

This output confirms that the WHERE clause fetched all players except “John”.

From the examples, we can conclude that the WHERE clause filters the rows returned by the SELECT statement. That’s all you need to know about the Postgres WHERE clause.

Conclusion

In PostgreSQL, the WHERE clause can be used within different queries/statements such as SELECT, DELETE, UPDATE, etc. The WHERE clause filters the results on the basis of different conditions. The condition represents a single Boolean expression or set of multiple Boolean expressions. In PostgreSQL, different operators can be used within the specified condition, such as AND, OR, !=, >, etc. This write-up considered some examples to summarize the basics of the Postgres WHERE clause with the SELECT query.