Select Query with WHERE Clause in PostgreSQL

The SELECT query in PostgreSQL is used to retrieve data from one or more tables. When you want to filter the rows returned by a query, you can use the WHERE clause to specify conditions. This clause helps narrow down the results by applying logical expressions based on column values.

In this tutorial, we will explore the syntax of the WHERE clause, demonstrate its usage with practical examples, and explain each query to understand its application in real-world scenarios.

Basic Syntax of SELECT Query with WHERE Clause

</>
Copy
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Explanation:

  • column1, column2: The columns you want to retrieve.
  • table_name: The name of the table from which to retrieve data.
  • condition: A logical expression to filter rows. Only rows that satisfy the condition are returned.

Example 1: Basic Filtering with WHERE Clause

Let’s create a table named employees and insert some sample data:

</>
Copy
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

INSERT INTO employees (employee_name, department, salary)
VALUES 
('Alice', 'HR', 50000),
('Bob', 'IT', 60000),
('Charlie', 'Finance', 70000),
('David', 'Marketing', 55000),
('Eve', 'IT', 72000);

To retrieve employees from the IT department, use the following query:

</>
Copy
SELECT employee_id, employee_name, salary
FROM employees
WHERE department = 'IT';

Explanation: The query selects the employee_id, employee_name, and salary columns from the employees table, filtering rows where the department is equal to 'IT'. Only employees belonging to the IT department are returned.

Output:

PostgreSQL - Select Query with WHERE Clause - Basic Filtering

Example 2: Using Comparison Operators in WHERE Clause

Comparison operators such as =, !=, <, <=, >, and >= can be used in the WHERE clause to filter data based on specific conditions. To retrieve employees with a salary greater than 60000:

</>
Copy
SELECT employee_name, department, salary
FROM employees
WHERE salary > 60000;

Explanation: This query selects all employees whose salary exceeds 60000. The > operator compares the salary column for each row to the value 60000.

Output:

PostgreSQL - Select Query with WHERE Clause Using Comparison Operators

Example 3: Combining Conditions with Logical Operators in WHERE Clause

Logical operators such as AND, OR, and NOT can be used to combine multiple conditions in the WHERE clause. For instance, to retrieve employees in the IT department with a salary greater than 60000:

</>
Copy
SELECT employee_name, salary
FROM employees
WHERE department = 'IT' AND salary > 60000;

Explanation: This query filters rows where the department is 'IT' and the salary is greater than 60000. Both conditions must be true for a row to be included in the result.

Output:

PostgreSQL - Select Query with WHERE Clause - Combining Conditions with Logical Operators

You can also use OR to retrieve employees meeting either of two conditions:

</>
Copy
SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT' OR salary > 70000;

Explanation: The query retrieves rows where either the department is 'IT' or the salary is greater than 70000. This is useful for retrieving data based on multiple alternative criteria.

Output:

PostgreSQL - Select Query with WHERE Clause - Combining Conditions with Logical Operators - Second Example

Conclusion

The WHERE clause is a fundamental feature of PostgreSQL for filtering query results based on specified conditions. By using comparison operators, logical operators, and combining conditions, you can retrieve precise subsets of data to meet your needs.