In PostgreSQL, the WHERE clause is used to filter records based on specified conditions.
It allows us to retrieve only those records that match our requirements.
Real-life example:
Imagine a university database containing 5,000 student records.
The Registrar Office may need to:
Instead of displaying all records, we can use the WHERE clause to retrieve specific records.
SELECT column1, column2
FROM table_name
WHERE condition;
Understanding the syntax:
SELECT: Specifies the columns to retrieve.FROM: Specifies the table.WHERE: Specifies the filtering condition.condition: Defines which records should be displayed.Let’s create a students table.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
age INTEGER,
city VARCHAR(50),
department VARCHAR(50),
fee NUMERIC(10, 2)
);
Insert sample records:
INSERT INTO students
(student_id, student_name, age, city, department, fee)
VALUES
(1, 'Ali', 20, 'Multan', 'Computer Science', 50000),
(2, 'Ahmad', 22, 'Lahore', 'Information Technology', 45000),
(3, 'Usman', 19, 'Multan', 'Mathematics', 40000),
(4, 'Sara', 21, 'Lahore', 'Computer Science', 50000),
(5, 'Ayesha', 23, 'Dera Ghazi Khan', 'Information Technology', 45000),
(6, 'Hassan', 18, 'Multan', 'Computer Science', 50000);
Suppose we want to find a student whose ID is 1.
SELECT *
FROM students
WHERE student_id = 1;
This query displays only the record where student_id equals 1.
Comparison operators are used to compare values in SQL conditions.
| Operator | Meaning | Example |
|---|---|---|
= |
Equal to | age = 20 |
<> |
Not equal to | age <> 20 |
!= |
Not equal to | age != 20 |
> |
Greater than | age > 20 |
< |
Less than | age < 20 |
>= |
Greater than or equal to | age >= 20 |
<= |
Less than or equal to | age <= 20 |
Find students from Multan.
SELECT *
FROM students
WHERE city = 'Multan';
)
Find students who are not from Multan.
SELECT *
FROM students
WHERE city <> 'Multan';
Find students older than 20.
SELECT *
FROM students
WHERE age > 20;
Find students whose age is 20 or above.
SELECT *
FROM students
WHERE age >= 20;
Both conditions must be satisfied.
## 7. Filter Records Using OR
The `OR` operator is used when at least one of the specified conditions must be true.
**Example:** Find students who belong to Multan or Lahore.
```sql
SELECT *
FROM students
WHERE city = 'Multan'
OR city = 'Lahore';
A record is displayed if either condition is true.
The NOT operator reverses a condition.
Example: Find students who do not belong to Multan.
SELECT *
FROM students
WHERE NOT city = 'Multan';
The same result can be obtained using:
SELECT *
FROM students
WHERE city <> 'Multan';
The IN operator checks whether a value matches any value in a given list.
Example: Find students from Multan, Lahore, or Islamabad.
SELECT *
FROM students
WHERE city IN ('Multan', 'Lahore', 'Islamabad');
This is shorter than writing multiple OR conditions.
The BETWEEN operator retrieves values within a specified range.
Example: Find students whose ages are between 20 and 22.
SELECT *
FROM students
WHERE age BETWEEN 20 AND 22;
Important: BETWEEN includes both boundary values. In this example, students aged 20, 21, and 22 are included.
The LIKE operator is used to search for a particular pattern in text values.
It uses two important wildcard characters:
| Wildcard | Meaning |
|---|---|
% |
Matches zero or more characters. |
_ |
Matches exactly one character. |
SELECT *
FROM students
WHERE student_name LIKE 'A%';
This finds names starting with A.
SELECT *
FROM students
WHERE student_name LIKE '%a';
This finds names ending with a.
SELECT *
FROM students
WHERE student_name LIKE '%s%';
This finds names containing the letter s.
Note: LIKE is case-sensitive in PostgreSQL. Use ILIKE for case-insensitive pattern matching.
Example:
SELECT *
FROM students
WHERE student_name ILIKE 'a%';
This finds names starting with A or a.
In PostgreSQL, NULL represents a missing or unknown value.
To find records where a column has no value, use IS NULL.
SELECT *
FROM students
WHERE city IS NULL;
To find records where a column contains a value, use IS NOT NULL.
SELECT *
FROM students
WHERE city IS NOT NULL;
Important: Do not use = NULL to check for NULL values.
We can combine comparison operators and logical operators to create more specific queries.
Example: Find students from Multan or Lahore who are at least 20 years old.
SELECT *
FROM students
WHERE (city = 'Multan' OR city = 'Lahore')
AND age >= 20;
Parentheses help control the order in which conditions are evaluated.
Try the following queries:
Example 1: Find all students from Lahore.
SELECT *
FROM students
WHERE city = 'Lahore';
Example 2: Find students whose fee is 50,000.
SELECT *
FROM students
WHERE fee = 50000;
Example 3: Find students older than 20.
SELECT student_name, age
FROM students
WHERE age > 20;
Example 4: Find Computer Science students.
SELECT *
FROM students
WHERE department = 'Computer Science';
Example 5: Find students from Multan with a fee of 50,000.
SELECT *
FROM students
WHERE city = 'Multan'
AND fee = 50000;
Example 6: Find students whose names start with S.
SELECT *
FROM students
WHERE student_name LIKE 'S%';
Use the students table created in this lesson.
Exercise 1: Basic Filtering
Write queries to:
Exercise 2: Comparison Operators
Write queries to:
Exercise 3: Logical Operators
Write queries to:
Exercise 4: IN and BETWEEN
Write queries to:
Exercise 5: LIKE
Write queries to:
= and <>?% and _ represent in LIKE?| SQL Keyword / Operator | Purpose |
|---|---|
WHERE |
Filters records. |
= |
Checks equality. |
<> |
Checks inequality. |
> / < |
Compares values. |
AND |
Requires all conditions to be true. |
OR |
Requires at least one condition to be true. |
NOT |
Reverses a condition. |
IN |
Matches values from a list. |
BETWEEN |
Checks a range. |
LIKE |
Matches a text pattern. |
ILIKE |
Matches a text pattern without case sensitivity. |
IS NULL |
Finds missing values. |
Next Lesson: ORDER BY Clause in PostgreSQL
Students will learn how to sort query results in ascending and descending order using one or multiple columns.