Understanding PostgreSQL WHERE Clause

Basic Usage of WHERE Clause:

The WHERE clause in PostgreSQL is used to filter records based on specified conditions. It is crucial for narrowing down the result set to include only the rows that meet the defined criteria.

Syntax:

The basic syntax of the WHERE clause is as follows: SELECT column1, column2 FROM table_name WHERE condition;

Example with Single Condition:

To retrieve employees from the 'employees' table who are in the 'Sales' department, you would use:

SELECT * FROM employees WHERE department = 'Sales';
      

Using Multiple Conditions:

You can combine multiple conditions using AND, OR operators. For example, to find employees in 'Sales' department and with a salary greater than 50000:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;
      

Using WHERE with LIKE Operator:

The LIKE operator is used to search for a specified pattern in a column. For example, to find employees whose names start with 'J':

SELECT * FROM employees WHERE name LIKE 'J%';
      

Filtering with IN Operator:

The IN operator allows you to specify multiple values in a WHERE clause. For example, to find employees in either 'Sales' or 'Marketing':

SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');
      

Using WHERE with BETWEEN Operator:

The BETWEEN operator selects values within a given range. For example, to find employees with salaries between 30000 and 60000:

SELECT * FROM employees WHERE salary BETWEEN 30000 AND 60000;
      

Combining WHERE with NOT Operator:

The NOT operator negates a condition. For example, to find employees not in the 'HR' department:

SELECT * FROM employees WHERE NOT department = 'HR';
      

WHERE with IS NULL:

To find records with NULL values, use IS NULL. For example, to find employees without a manager assigned:

SELECT * FROM employees WHERE manager_id IS NULL;
      

Practical Example - Combining Multiple Operators:

To find employees in 'Sales' department, with salary greater than 50000, and whose names start with 'A', you can combine conditions:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000 AND name LIKE 'A%';