PostgreSQL WHERE

 

PostgreSQL WHERE



Summary: in this tutorial, you will learn how to use PostgreSQL WHERE clause to filter rows returned by a SELECT statement.

The SELECT the statement returns all rows from one or more columns in a table. To select rows that satisfy a specified condition, you use a WHERE clause.

PostgreSQL WHERE clause overview

The syntax of the PostgreSQL WHERE a clause is as follows:

SELECT select_list FROM table_name WHERE condition ORDER BY sort_expression

The WHERE the clause appears right after the FROM clause of the SELECT statement.  The WHERE clause uses the condition to filter the rows returned from the SELECT clause.

The condition must evaluate to true, false, or unknown. It can be a boolean expression or a combination of boolean expressions using the AND and OR operators.

The query returns only rows that satisfy the condition in the WHERE clause. In other words, only rows that cause the condition evaluates to true will be included in the result set.

PostgreSQL evaluates the WHERE clause after the FROM clause and before the SELECT and ORDER BY clause:

If you use column aliases in the SELECT clause, you cannot use them in the WHERE clause.

Besides the SELECT the statement, you can use the WHERE clause in the UPDATE and DELETE statement to specify rows to be updated or deleted.

To form the condition in the WHERE clause, you use comparison and logical operators:

OperatorDescription
=Equal
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
<> or !=Not equal
ANDLogical operator AND
ORLogical operator OR
INReturn true if a value matches any value in a list
BETWEENReturn true if a value is between a range of values
LIKEReturn true if a value matches a pattern
IS NULLReturn true if a value is NULL
NOTNegate the result of other operators

PostgreSQL WHERE clause examples

Let’s practice with some examples of using the WHERE clause. We will use the customer table from the sample database for the demonstration.

1)  Using WHERE clause with the equal (=) operator example

The following statement uses the WHERE clause customers whose first names are Jamie:

SELECT last_name, first_name FROM customer WHERE first_name = 'Jamie';

2) Using the WHERE clause with the AND operator example

The following example finds customers whose first name and last name are Jamie and rice by using the AND logical operator to combine two Boolean expressions:

SELECT last_name, first_name FROM customer WHERE first_name = 'Jamie' AND last_name = 'Rice';

3) Using the WHERE clause with the OR operator example

This example finds the customers whose last name is Rodriguez or the first name is Adam by using the OR operator:

SELECT first_name, last_name FROM customer WHERE last_name = 'Rodriguez' OR first_name = 'Adam';

4) Using the WHERE clause with the IN operator example

If you want to match a string with any string in a list, you can use the IN operator.

For example, the following statement returns customers whose first name is Ann, or Anne, or Annie:

SELECT first_name, last_name FROM customer WHERE first_name IN ('Ann','Anne','Annie');

5) Using the WHERE clause with the LIKE operator example

To find a string that matches a specified pattern, you use the LIKE operator. The following example returns all customers whose first names start with the string Ann:

SELECT first_name, last_name FROM customer WHERE first_name LIKE 'Ann%'

The % is called a wildcard that matches any string. The 'Ann%' pattern matches any string that starts with 'Ann'.

6) Using the WHERE clause with the BETWEEN operator example

The following example finds customers whose first names start with the letter A and contains 3 to 5 characters by using the BETWEEN operator.

The BETWEEN the operator returns true if a value is in a range of values.

SELECT first_name, LENGTH(first_name) name_length FROM customer WHERE first_name LIKE 'A%' AND LENGTH(first_name) BETWEEN 3 AND 5 ORDER BY name_length;

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

7) Using the WHERE clause with the not equal operator (<>) example

This example finds customers whose first names start with Bra and last names are not Motley:

SELECT first_name, last_name FROM customer WHERE first_name LIKE 'Bra%' AND last_name <> 'Motley';

Note that you can use the != operator and <> operator interchangeably because they are equivalent.

In this tutorial, you have learned how to use PostgreSQL WHERE clause in the SELECT statement to filter rows based on a specified condition.

Reactions

Post a Comment

0 Comments