PostgreSQL WHERE

PostgreSQL WHERE Clause

The WHERE clause in PostgreSQL is used to filter records from a table based on a specified condition. It is one of the most important parts of SQL queries as it helps restrict the rows returned by a SELECT, UPDATE, DELETE, or other SQL commands.

1. Basic Syntax of the WHERE Clause

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

• condition: The criteria that must be met for rows to be included in the result.
• Example condition: column_name = value, column_name > value, column_name LIKE pattern, etc.

2. Common Conditions Used with WHERE

Equality (=)

SELECT * FROM employees
WHERE department = 'HR';

• This query returns all employees who work in the "HR" department.

Inequality (!= or <>)

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

• This returns employees not in the "HR" department.

Greater Than (>)

SELECT * FROM employees
WHERE salary > 50000;

• This fetches employees with a salary greater than 50,000.

Less Than (<)

SELECT * FROM employees
WHERE salary < 50000;

• This fetches employees with a salary less than 50,000.

Greater Than or Equal To (>=)

SELECT * FROM employees
WHERE salary >= 50000;

• Fetches employees earning 50,000 or more.

Less Than or Equal To (<=)

SELECT * FROM employees
WHERE salary <= 50000;

• Fetches employees earning 50,000 or less.

3. Logical Operators with WHERE

You can use logical operators to combine multiple conditions.

AND (Both conditions must be true)

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

• This returns employees who are in the "HR" department and earn more than 50,000.

OR (Either condition can be true)

SELECT * FROM employees
WHERE department = 'HR' OR department = 'Finance';

• Returns employees who are in either the "HR" or "Finance" department.

NOT (Negates the condition)

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

• Fetches employees not in the "HR" department.

4. Other Comparison Operators with WHERE

BETWEEN (Range Check)

SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

• This fetches products with a price between 100 and 500 (inclusive).

LIKE (Pattern Matching)

SELECT * FROM customers
WHERE first_name LIKE 'J%';

• This returns customers whose first name starts with "J".

ILIKE (Case-Insensitive Pattern Matching

SELECT * FROM customers
WHERE first_name ILIKE 'j%';

• This returns customers whose first name starts with "j" (case-insensitive).

IN (Multiple Possible Values)

SELECT * FROM employees
WHERE department IN ('HR', 'Finance');

• Returns employees who are in either the "HR" or "Finance" department.

IS NULL (Check for NULL values)

SELECT * FROM employees
WHERE email IS NULL;

• Returns employees who do not have an email.

IS NOT NULL (Check for non-NULL values)

SELECT * FROM employees
WHERE email IS NOT NULL;

• Returns employees who have an email.

5. Example Queries Using WHERE

Example 1: Find Employees in 'Sales' Department

SELECT * FROM employees
WHERE department = 'Sales';

Example 2: Find Products Between $20 and $100

SELECT * FROM products
WHERE price BETWEEN 20 AND 100;

Example 3: Find Customers Whose Names Start with 'M'

SELECT * FROM customers
WHERE first_name LIKE 'M%';

Example 4: Get Employees Who Have a Salary Greater Than $80,000

SELECT * FROM employees
WHERE salary > 80000;

Example 5: Find Employees in 'HR' or 'Finance' Departments

SELECT * FROM employees
WHERE department IN ('HR', 'Finance');

Example 6: Check for Employees with NULL Email

SELECT * FROM employees
WHERE email IS NULL;

6. Combining Conditions with Parentheses

When using multiple logical operators, use parentheses to control the order of evaluation.

Example: Complex Condition

SELECT * FROM employees
WHERE (department = 'HR' AND salary > 50000) OR (department = 'Finance' AND salary < 60000);

• This finds employees in the "HR" department earning more than 50,000 or employees in the "Finance" department earning less than 60,000.

7. Summary

Condition	Description
=	Equal to
!= or <>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
BETWEEN	Range of values
LIKE	Pattern matching (case-sensitive)
ILIKE	Pattern matching (case-insensitive)
IN	List of possible values
IS NULL	Check for NULL values
IS NOT NULL	Check for non-NULL values
AND	Both conditions must be true
OR	At least one condition must be true
NOT	Negates the condition

Would you like help building more complex queries with WHERE clauses? 🚀