Home » PostgreSQL Where Clause

PostgreSQL Where Clause

by Online Tutorials Library

PostgreSQL WHERE Clause

In this section, we are going to understand the working of PostgreSQL where clause, which is used to filter the rows returned by a Select command.

The WHERE condition is used to describe a condition while we get data from a table or a Join various tables. It is commonly used with SELECT, UPDATE, and DELETE commands to filter the output. It returns the exact result only when the condition is fulfilled.

Note: To select rows that satisfy a detailed condition, we use a WHERE clause. And the SELECT command returns all rows from one or more columns in a table.

The syntax of PostgreSQL WHERE clause

The syntax of PostgreSQL WHERE condition is as below:

In the Select command, the WHERE condition comes after the FROM clause. And the condition is used to filter the rows returned from the SELECT command.

The where clause could be the Boolean expression or a grouping of Boolean expressions if we use the AND & OR operators. And the clause must assess to true, false, or unknown.

The WHERE condition works on the below format:

It begins from the From clause → then it performs the Where condition → after that it will Select the particular columns → and then perform by the Order by clause.

PostgreSQL WHERE Clause

The WHERE condition does not support the column aliases under the SELECT Command. The Where clause supports DELETE and UPDATE command, which is used to define the removed and updated rows.

The WHERE condition can be used with logical and comparison operators, as shown in the below table:

Different Operators Description
AND Logical operator AND
OR Logical operator OR
= Equal
> Greater than
< Less than
<> or != Not equal
>= Greater than or equal
<= Less than or equal
IN The IN operator will return true if a value matches any value in a list
LIKE The LIKE operator is used to return true if a value matches a pattern
BETWEEN The BETWEEN operator is used to return true if a value is between a range of values
NOT Negate the result of other operators
IS NULL It is used to return true if a value is NULL.

Examples of PostgreSQL WHERE condition

Lets us see some examples where we learn how the WHERE clause works in PostgreSQL.

For this, we are going to take the Employee table, which we created in the earlier section of the PostgreSQL tutorial.

The below screenshot defines the different columns present in the Employee table:

PostgreSQL WHERE Clause

In the above table, we will perform different types of operators using the where clause.

  • Example of AND operator using WHERE clause

In the below example, we will be using the And logical operator for combining the two Boolean expressions in the employee table to find first_name is Mia and last_name is Smith.

Output

After executing the above command, we will get the below output:

PostgreSQL WHERE Clause

  • Example of the (=) equal operator using WHERE clause

In the below command, we are using the Equal (=) operator in the employee table using the where condition to identify the records whose last_name is equal to smith.

Output

After executing the above command, we will get the below output that fetches the two records whose last _name is equal to smith.

PostgreSQL WHERE Clause

  • Example of OR operator using WHERE clause

In the below example, we will be using the OR logical operator in the employee table to identify the first_name as Megan, and last_name as Will.

Output

After implementing the above command, we will get the below output:

PostgreSQL WHERE Clause

  • Examples of the LIKE operator using the WHERE clause

Here, we are going to use the LIKE operator for identifying a string, which matches a defined design.

The below command displays those records whose last_name starts with the string smi in the employee table.

Output

After executing the above command, we will get the below output, where the above query fetches the four records whose last_name starts with smi string:

PostgreSQL WHERE Clause

Note: In the above command, the % is known as wildcard, which helps to match any string. And the ‘smi%’ pattern matches those strings which begins with ‘smi’.

  • Examples of IN operator using WHERE clause

Here, we will use the IN operator to match a string with any string in a list.

The below command displays those records whose last_name is smi, smith, in the employee table.

Output

Once we implemented the above command, we will get the below output:

PostgreSQL WHERE Clause

  • Example of BETWEEN operator using the WHERE clause

In the below command, we will use Between operator to show the last_name, which ends with the string ith and having 2 to 6 characters in the employee table.

Note: In this, we used the LENGTH() function to get the number of an input string characters.

Output

Once we execute the above command, we will get the below output, where the above query fetches the two records which match the ith string, and the name length is between 2 and 6.

PostgreSQL WHERE Clause

  • Examples of not equal operator (<>) using the WHERE clause

In the below example, we will use the Not equal <> operator to identify those employees whose address begins with New string, and first_name is not equal to Olivia.

Output

Once we execute the above command, we will get the below output, where the query fetches the two records whose address matches the New string, and the first_name is not equal to Olivia.

PostgreSQL WHERE Clause

Note: We can use both the <> operator and != operator as they are similar as we can see in the below command:

Output

After executing the above command, we will get the below output, where the query fetches the one records whose address starts with the new string, and the first_name is not equal to John.

PostgreSQL WHERE Clause


You may also like