PostgreSQL WHERE Clause

In this PostgreSQL tutorial, we will learn PostgreSQL WHERE Condition and its syntax with practical examples to show its usage.

What is the PostgreSQL WHERE condition?

In PostgreSQL, the WHERE condition is used to define the filtering requirements for obtaining data from one or more tables. It enables you to only retrieve the rows that satisfy the query’s criteria.

Although it can also be used with other SQL statements like UPDATE and DELETE, the WHERE clause is primarily used together with the SELECT statement.

The following is the syntax of the WHERE Condition.

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

Here, table_name is the name of the table you want to query, and column1, column2, column3,…, represent the columns you want to get and condition is a representation of the filtering criteria.

Common operators for the WHERE condition include:

• Comparison Operators: “=”, “<>”, “>”, “<“, “>=”, “<=”
• Logical Operators: “AND”, “OR”, “NOT”
• Pattern Matching Operators: “LIKE”, “ILIKE”
• Range Operators: “BETWEEN”, “NOT BETWEEN”
• Membership Operators: “IN”, “NOT IN”
• NULL Operators: “IS NULL”, “IS NOT NULL”

Let’s see some examples to show the usage of the WHERE condition in PostgreSQL.

PostgreSQL WHERE Condition Getting Specific Rows

In this example, we will know how to get particular rows from the table using the WHERE condition.

Suppose a table called “employees”, which has the columns “employee_id”, “first_name”, “last_name” and “salary”.

Now, we will see how to find employees with a salary of more than $50,000 by using the below query.

SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > 50000;

The above query gets the employee_id, first_name, and last_name of the employees whose salary is greater than $50,000. The WHERE condition defines the filtering criteria by comparing the value 50000 with the “salary” column using the “>” (greater than) operator.

So there are four employees William, Olivia, James, and Benjamin with a salary greater than $50000.

PostgreSQL WHERE Condition Adding Multiple Conditions

To get the employees with a salary greater than $50,000 and hired after January 1, 2023, use the below query.

SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE salary > 50000 AND hire_date > '2023-01-01';

The above query gets the employee_id, first_name, and last_name, salary, and hire_date of the employees who meet two specific conditions. WHERE salary > 50000 AND hire_date > ‘2023-01-01’, This line indeed sets the conditions for filtering the data.

Therefore, the query retrieves the employee _id, first_name, last_name, salary, and hire_date of employees who have a salary greater than $50,000 and were hired after January 1, 2023.

PostgreSQL WHERE Condition Pattern Matching

To get the employees whose last names begin with “Smi” or any other letters, you can use the LIKE operator with a wildcard(“%”) in the WHERE condition.

SELECT employee_id, first_name, last_name
FROM employees
WHERE last_name LIKE 'Smi%';

The above query gets the employee’s employee_id, first_name, and last_name whose last_name starts with “Smi”. The WHERE condition uses the “LIKE” operator along with a pattern.

The pattern “Smi%” specifies that the last name should start with “Smi” followed by any number of characters. The ‘%’ wildcard represents any number of characters. So there is one employee Olivia with the last name Smith which starts with “Smi”.

PostgreSQL WHERE Condition Null Values

To get the employees with a null value in the “department_id” column, use the IS NULL operator in the WHERE condition.

SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id IS NULL;

The above query gets the employee’s employee_id, first_name, and last_name of employees whose “department_id” column has a null value. The WHERE condition uses the “IS NULL” operator to check if the “department_id” is null.

So there is one employee Emma Brown who doesn’t belong to any department.

Conclusion

In this PostgreSQL tutorial, we learned about the PostgreSQL WHERE condition, with different examples like using specific rows, adding multiple conditions, pattern matching, and null values in the WHERE condition.

You may like to read:

• How to Create View in PostgreSQL
• ILIKE Case Insensitive in PostgreSQL