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? 🚀