WHERE statement in SQL (filtering the data)

In SQL, 'WHERE' keyword is used to retrieve those rows in a dataset which satisfy a particular condition.

For this tutorial we shall be using the following dataset:

Syntax:

SELECT *

FROM <table name>

WHERE <specify the condition to filter>

Filtering numeric columns...

Let us filter the rows where Loan Amount is more than 150000.

select * from table
where Loan_Amount  > 150000;

Similarly, one can use the following operators with numeric columns to filter the datasets:

BETWEEN and AND keywords

Task: Retrieve the rows where Loan Amount is between 150000 and 250000

select * from table
where Loan_Amount BETWEEN 150000 AND 250000;

Note: In this both 150000 and 250000 would be inclusive.

Filtering string / character columns...

Task: Retrieve the rows where Education_level is illiterate.

select * from table
where education_level = 'illiterate';

Task: Retrieve the rows where Education_level is NOT illiterate.

select * from table
where education_level <> 'illiterate';

Note: Not equal to operator in SQL is < > ; some people get confused and believe SQL uses != (but it is not so!)

IN and NOT IN

In SQL, In keyword is used to filter multiple values in a column, NOT IN excludes the specified values in the resultant dataset.

Task: Retrieve the rows where Education_level is either graduate or post-graduate.

select * from table
where education_level IN ('Graduate','Post-Graduate');

Task: Retrieve the rows where Education_level is either graduate or post-graduate.

select * from table
where education_level IN ('Graduate','Post-Graduate');

Task: Retrieve the rows where Education_level is neither graduate nor post-graduate.

select * from table
where education_level NOT IN ('Graduate','Post-Graduate');

Pattern Matching:

For the following exercises we shall leverage hospital_data:

Suppose we want to filter the information of the patients whose name starts with 'Al', for this we shall use LIKE keyword:

select * from hospital_data
where Patient_name like 'Al%'

% symbol represents 0 or more characters can appear after string pattern 'Al'. On similar lines, what should be written to ascertain information about patients whose name end with 'a'?

where patient_name like '%a';

To get the data of patients whose name contains 'ss' we write:

where patient_name like '%ss%';

Filtering for multiple conditions:

Filter the rows where patient is female and is above 40 years of age. Since both the conditions need to be fulfilled, for this we use AND keyword:

select * from hospital_data
where gender = 'F' AND age > 40;

Filter the rows where either patient is female or is above 40 years of age. Since at least one of the conditions need to be fulfilled, for this we use OR keyword:

select * from hospital_data
where (gender = 'F') OR (age > 40);

Filtering for missing data:

Missing values in SQL are represented by NULL keyword, thus to filter for rows having missing values in a column we use IS NULL.

select * from hospital_Data
where Length_of_stay IS NULL

Task: Filter for rows where there are no missing values in length_of_stay.

select * from hospital_Data
where length_of_stay IS NOT NULL;