Filtering Rows

What is filtering?

Filtering is the process of selecting rows based on conditions. For example, you may want to select all rows where the value in a column is greater than 10.

The WHERE function is used to do this. It takes a condition as an argument and returns only the rows that satisfy the condition.

Syntax

 SELECT *
FROM table_name
WHERE condition; 

The condition is a logical expression that evaluates to true or false. If the condition evaluates to true, the row is included in the result set; if the condition evaluates to false, the row is excluded from the result set.

Filtering numeric values

To create a condition, we need to use comparison operators. Here are some common comparison operators for numeric values:

Greater than

We use the > operator to filter rows where the value in a column is greater than a specified value.

Syntax

 SELECT *
FROM table_name
WHERE column_name > value; 

Example

We have a table called students with the following data:

To select all students who are older than 21, we can use the following SQL query:

 SELECT *
FROM students
WHERE age > 21; 

Output

Exercise

4.1 Run the following query to select all students who older than 21

4.2 Write a query to select students who are older than 20

Greater than or equal to

We use the >= operator to filter rows where the value in a column is greater than or equal to a specified value.

Syntax

 SELECT *
FROM table_name
WHERE column_name >= value; 

Example

We can use the following SQL query to select all students who are 21 or older:

 SELECT *
FROM students
WHERE age >= 21; 

Output

Exercise

4.3 Run the following query to select all students who are 21 or older

4.4 Write a query to select students who are 20 or older

Less than

Syntax

 SELECT *
FROM table_name
WHERE column_name < value; 

Example

To select all students who are younger than 21, we can use the following SQL query:

 SELECT *
FROM students
WHERE age < 21; 

Output

Exercise

4.5 Run the following query to select all students who are younger than 21

4.6 Write a query to select students who are younger than 22

Less than or equal to

Syntax

 SELECT *
FROM table_name
WHERE column_name <= value; 

We can use the following SQL query to select all students who are 21 or younger:

Example

 SELECT *
FROM students
WHERE age <= 21; 

Output

Exercise

4.7 Run the following query to select all students who are 21 or younger

4.8 Write a query to select students who are 22 or younger

Equal to

Syntax

 SELECT *
FROM table_name
WHERE column_name = value; 

We can use the following SQL query to select all students who are 21 years old:

Example

 SELECT *
FROM students
WHERE age = 21; 

Output

Exercise

4.9 Run the following query to select all students who are 21 years old

4.10 Write a query to select students who are 22 years old

Filtering string values

We can filter rows based on string values. We’ll use the following operators to filter rows based on conditions:

Equal to

Syntax

 SELECT *
FROM table_name
WHERE column_name = 'value'; 

Example

We can use the following SQL query to select all students who are majoring in Mathematics. We need to use quotes around the string value.

 SELECT *
FROM students
WHERE major = 'Mathematics'; 

Output

Exercise

4.11 Run the following query to select all students who are majoring in Mathematics

4.12 Write a query to select students who are majoring in Biology

Not equal to

Syntax

 SELECT *
FROM table_name
WHERE column_name != 'value'; 

Example

 SELECT *
FROM students
WHERE major != 'Mathematics'; 

This query will return all students who are not majoring in Mathematics.

Output

Exercise

4.13 Run the following query to select all students who are NOT majoring in Mathematics

4.14 Write a query to select students who are NOT majoring in Biology

LIKE

The LIKE operator is used to match a pattern. The % symbol is used as a wildcard character to match any sequence of characters.

Syntax

 SELECT *
FROM table_name
WHERE column_name LIKE 'pattern'; 

Example

We can use the LIKE operator to select all students who are majoring in a field that starts with “Math”.

 SELECT *
FROM students
WHERE major LIKE 'Math%'; 

Output

Exercise

4.15 Run the following query to select all students who are majoring in a field that starts with “Math”

4.16 Write a query to select students who are majoring in a field that starts with “Bio”

IN and NOT IN

The IN and NOT IN operators are used to match any value in a list of values.

Syntax

 SELECT *
FROM table_name
WHERE column_name IN ('value1', 'value2', ...); 

 SELECT *
FROM table_name
WHERE column_name NOT IN ('value1', 'value2', ...); 

Example

IN

We can use the IN operator to select all students who are majoring in Mathematics or Biology. We need to enclose the values in parentheses, separated by commas.

 SELECT *
FROM students
WHERE major IN ('Mathematics', 'Biology'); 

Output

NOT IN

We can use the NOT IN operator to select all students who are NOT majoring in Mathematics or Biology. We need to enclose the values in parentheses, separated by commas.

 SELECT *
FROM students
WHERE major NOT IN ('Mathematics', 'Biology'); 

Output

Exercise

4.17 Run the following query to select all students who are majoring in Mathematics or Biology

4.18 Write a query to select students who are NOT majoring in Computer Science or Mathematics

Filtering Rows with Multiple Conditions

We can filter rows based on multiple conditions using logical operators such as AND , OR , and NOT .

AND

The AND operator is used to combine two or more conditions. It returns rows that satisfy all the conditions.

Syntax

 SELECT *
FROM table_name
WHERE condition1 AND condition2; 

Example

We can use the AND operator to select all students who are majoring in Mathematics and are older than 20.

 SELECT *
FROM students
WHERE major = 'Mathematics' AND age > 20; 

Output

Exercise

4.19 Run the following query to select all students who are majoring in Mathematics and are older than 20.

4.20 Write a query to select students who are majoring in Biology and their age is equal to 22.

OR

The OR operator is used to combine two or more conditions. It returns rows that satisfy at least one of the conditions.

Syntax

 SELECT *
FROM table_name
WHERE condition1 OR condition2; 

Example

We can use the OR operator to select all students who are majoring in Mathematics or are older than 20.

 SELECT *
FROM students
WHERE major = 'Mathematics' OR age > 20; 

Output

Exercise

4.21 Run the following query to select all students who are majoring in Mathematics or are older than 20

4.22 Write a query to select students who are majoring in Biology or are younger than 22

NOT

The NOT operator is used to negate a condition. It returns rows that do not satisfy the condition.

Syntax

 SELECT *
FROM table_name
WHERE NOT condition; 

Example

We can use the NOT operator to select all students who are NOT majoring in Mathematics.

 SELECT *
FROM students
WHERE NOT major = 'Mathematics'; 

Output

Exercise

4.23 Run the following query to select all students who are NOT majoring in Mathematics

4.24 Write a query to select students who are NOT majoring in Biology