PostgreSQL - WHERE clause

The PostgreSQL WHERE clause is a critical component of SQL queries, allowing users to filter records based on specified conditions. In this tutorial, we'll explore how the WHERE clause works in PostgreSQL, its integration with the SELECT statement, and various examples.

By using the WHERE clause, we can retrieve only the rows that meet our specific criteria, making it an essential tool for data manipulation and querying in PostgreSQL. Whether we are working with simple conditions or complex comparisons by mastering the WHERE clause can significantly improve query efficiency.

PostgreSQL WHERE Clause

• The PostgreSQL WHERE clause is used to filter rows in a SQL query based on specific conditions. It ensures that only rows that meet the given criteria are included in the query result.
  
• The WHERE clause can be used in SELECT, UPDATE, DELETE and other SQL statements to control which records are affected or returned.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Explanation:

• SELECT column1, column2, ...: Specifies the columns that you want to retrieve. You can select one or more columns from a table.
  
• FROM table_name: Indicates the table from which the data will be selected. This is the table that contains the rows to be filtered.
  
• WHERE condition: This is the core of the WHERE clause, where you define the condition that must be met for rows to be included in the result set. Only rows that satisfy the condition will be returned by the query.

The WHERE clause can also be used with UPDATE and DELETE statements to specify rows to be updated or deleted.

Examples of PostgreSQL WHERE clause

Now, let us look into some of the examples of WHERE Clause in PostgreSQL to better understand the concept.

Example 1: Using WHERE Clause with the Equal (=) Operator

Using WHERE clause with the equal (=) operator. Here we will be using the equal operator in the "customer" table of our sample database.

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

Output:

Explanation: The WHERE clause filters the results to include only those rows where the first_name is 'Kelly'.

Example 2: Using WHERE Clause with the AND Operator

Using the WHERE clause with the AND operator. Here we will be using the AND operator in the "customer" table of our sample database.

SELECT
    last_name,
    first_name
FROM
    customer
WHERE
    first_name = 'Kelly'
AND last_name = 'Knott';

Output:

Explanation: The WHERE clause filters the results to include only those rows where both conditions (first_name = 'Kelly' AND last_name = 'Knott') are met.

Example 3: Using WHERE Clause with the OR Operator

Using the WHERE clause with the OR operator. Here we will be using the OR operator in the "customer" table of our sample database.

SELECT
    first_name,
    last_name
FROM
    customer
WHERE
    last_name = 'Cooper' OR 
    first_name = 'Jo';

Output:

Explanation: The WHERE clause filters the results to include rows where either condition (last_name = 'Cooper' OR first_name = 'Jo') is met.

Example 4: Using WHERE Clause with the IN Operator

Using the WHERE clause with the IN operator. The IN operator is used for string matching. Here we will be using the IN operator in the "customer" table of our sample database.

SELECT
    first_name,
    last_name
FROM
    customer
WHERE 
    first_name IN ('Kelly', 'Jo', ' Alexander');

Output:

Explanation: The WHERE clause filters the results to include rows where the first_name is either 'Kelly', 'Jo', or 'Alexander'.

Example 5: Using WHERE Clause with the LIKE Operator

Using the WHERE clause with the LIKE operator. The LIKE operator is used to find string matching a particular pattern. Here we will be using the LIKE operator in the "customer" table of our sample database.

SELECT
    first_name,
    last_name
FROM
    customer
WHERE 
    first_name LIKE 'Kath%';

Output:

Explanation: The LIKE operator with the pattern 'Kath%' filters the results to include rows where the first_name starts with 'Kath'.

Example 6: Using WHERE Clause with the BETWEEN Operator

Using the WHERE clause with the BETWEEN operator. The BETWEEN operator return if a value is in the mentioned range. Here we will be using the BETWEEN operator in the "customer" table of our sample database.

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

Output:

Explanation: The WHERE clause filters the results to include rows where the first_name starts with 'K' and the length of first_name is between 3 and 7 characters.

Example 7: Using WHERE Clause with the Not Equal Operator (<>)

Using the WHERE clause with the not equal operator (<>). Here we will be using the <> operator in the "customer" table of our sample database.

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

Output:

Explanation: The WHERE clause filters the results to include rows where the first_name starts with 'Bra' and the last_name is not 'Motley'.

Important Points About PostgreSQL WHERE clause

• The WHERE clause is used to filter records returned by the SELECT, UPDATE, and DELETE statements.
  
• The condition in the WHERE clause can evaluate to true, false, or unknown. It can be a single Boolean expression or a combination of expressions using logical operators.
  
• The WHERE clause is executed after the FROM clause and before the SELECT clause in a query.
  
• Avoid using functions on columns in the WHERE clause if possible, as this can prevent the use of indexes.

Conclusion

Understanding and effectively using the WHERE clause in PostgreSQL is fundamental to writing optimized SQL queries. This tutorial has covered how to apply the WHERE clause within the SELECT statement and provided practical examples of filtering data using various operators and conditions.