PostgreSQL: WHERE Clause – Filter Data

Welcome to The Coding College, where we simplify coding and programming for learners worldwide! In this tutorial, we’ll dive into using the WHERE clause in PostgreSQL to filter data from your database effectively.

What is the WHERE Clause?

The WHERE clause is used in SQL to specify conditions that must be met for rows to be included in the query result. It’s a crucial tool for retrieving targeted and relevant data from a table.

Syntax

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

• column1, column2, ...: The columns to retrieve.
• table_name: The table from which to fetch the data.
• condition: The filtering condition to apply.

Example: Sample Table

Let’s consider a table named employees:

Using the WHERE Clause

Example 1: Filter Employees in the IT Department

SELECT * FROM employees
WHERE department = 'IT';

Result:

Comparison Operators

Use comparison operators to set conditions:

• =: Equals
• != or <>: Not equals
• <: Less than
• >: Greater than
• <=: Less than or equal to
• >=: Greater than or equal to

Example 2: Filter Employees with Salary Above 60,000

SELECT name, salary FROM employees
WHERE salary > 60000;

Result:

Logical Operators

Combine conditions using logical operators:

• AND: Both conditions must be true.
• OR: At least one condition must be true.
• NOT: Negates the condition.

Example 3: Filter Employees in IT Department and Earning More Than 70,000

SELECT * FROM employees
WHERE department = 'IT' AND salary > 70000;

Result:

Example 4: Filter Employees in HR or Younger Than 30

SELECT name, department, age FROM employees
WHERE department = 'HR' OR age < 30;

Result:

Filter with Pattern Matching (LIKE)

Use LIKE with wildcards to filter data based on patterns.

• %: Matches zero or more characters.
• _: Matches a single character.

Example 5: Filter Names Starting with ‘A’

SELECT name FROM employees
WHERE name LIKE 'A%';

Result:

Filter with Ranges (BETWEEN)

Use BETWEEN to filter values within a range.

Example 6: Filter Employees Aged Between 25 and 35

SELECT name, age FROM employees
WHERE age BETWEEN 25 AND 35;

Result:

Filter Null Values (IS NULL / IS NOT NULL)

Filter rows with null or non-null values.

Example 7: Filter Employees Without a Department

SELECT * FROM employees
WHERE department IS NULL;

Learn More at The Coding College

Find more in-depth PostgreSQL tutorials and advanced guides at The Coding College. We prioritize user-focused content while adhering to Google’s E-E-A-T principles for accuracy and expertise.

Conclusion

The WHERE clause is an indispensable tool for filtering data in PostgreSQL. By mastering its syntax and operators, you can craft powerful queries tailored to your data needs.

For more PostgreSQL insights, stay connected with The Coding College!