SQL WHERE

The WHERE clause filters rows, so the result has only the rows that match a condition.

WHERE Syntax

Example

sql

SELECT column1, column2
FROM table_name
WHERE condition;

The database checks the condition for every row. Rows where it is true are kept; the rest are left out.

Filter by Text

This query shows the students from Karachi:

Example

sql

SELECT name, marks
FROM students
WHERE city = 'Karachi';

name	marks
Ali Khan	78
Bilal Ahmed	64
Hamza Raza	70

Text goes in single quotes. In many databases the text comparison is exact, so 'karachi' may not match 'Karachi'.

Filter by Number

Numbers are written without quotes:

Example

sql

SELECT name, age
FROM students
WHERE age > 20;

name	age
Ayesha Siddiqui	21
Bilal Ahmed	22
Sana Iqbal	23

Comparison Operators

Operator	Meaning	Example
`=`	Equal	`city = 'Lahore'`
`<>`	Not equal (many databases also accept `!=`)	`city <> 'Lahore'`
`>`	Greater than	`marks > 80`
`<`	Less than	`age < 20`
`>=`	Greater than or equal	`marks >= 85`
`<=`	Less than or equal	`age <= 19`

Students who scored 80 or more:

Example

sql

SELECT name, marks
FROM students
WHERE marks >= 80;

name	marks
Ayesha Siddiqui	85
Fatima Noor	92
Sana Iqbal	88

Courses that are not 3 months long:

Example

sql

SELECT title, duration_months
FROM courses
WHERE duration_months <> 3;

title	duration_months
Web Development	6
Python Programming	4

Checking for NULL

NULL means "no value". You cannot find it with =, because NULL is not equal to anything, not even another NULL. Use IS NULL or IS NOT NULL instead.

Example

sql

SELECT name
FROM students
WHERE course_id IS NULL;

name
Hamza Raza

A very common mistake is writing WHERE course_id = NULL. It returns no rows at all. Always write IS NULL.

WHERE Works in Other Statements Too

WHERE is not only for SELECT. You will use it with UPDATE and DELETE to choose which rows to change or remove. There, forgetting it can change the whole table, so it is important to understand it well now.

Exercise

Which query finds the students who have no course?

SELECT name FROM students WHERE course_id = NULL;SELECT name FROM students WHERE course_id = 0;SELECT name FROM students WHERE course_id IS NULL;SELECT name FROM students WHERE NULL;