Explaining SELECT Statement with WHERE clause

In this article we will walk you through the WHERE clause to fetch the data from datasets that meets the criteria. You will also learn to use search conditions using comparison or logical operators.

Problem 

You need to retrieve data that meets specific criteria or combine data from different sources / googlesheets that meets specific criteria.

Solution

Pre-requisites

Before going through this article we strongly recommend that you go through the articles:
- Getting started with SQL for Impact Measurement

- Explaining SELECT Statement

Purpose

The WHERE Clause is used to extract specific data from a single table/dataset or merging with other tables meeting the criteria while excluding other irrelevant data. In simpler words, It only retrieves a specific value from the table if the stated condition is met. For example, you want to see the information about the courses on SQL Basics only then information on other courses is irrelevant to you.

You can therefore limit the data that is retrieved using SQL's WHERE clause capability. Only those rows that match the criteria you specify in the WHERE clause are returned to you when retrieving data from a table. In addition to being utilised in SELECT statements, the WHERE clause is also used in statements like UPDATE and DELETE.

WHERE [search_condition]

[search_condition] = {column or expression} comparison-operator value

column or expression - Is the column of a table or expression
comparison-operator - operators like -<> etc
value - Any user alue or column name for comparison

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Let's learn WHERE clause  

Consider the table "courses" that contains columns : id , course name, No of lessons. 

There are 6 records in this dataset and can be accessed here.

1. To fetch the Course Name with ID equal to 3, so the WHERE clause with SELECT statement should look like

SELECT "Course Name" from courses where "ID" = 3

TIP : Replace "Course name" with column name , replace courses with dataset name and ID with column name with value that suits with your use case

This would produce the following results : 

2. To fetch the course information with No of lessons > 9, so the WHERE clause with SELECT statement should look like

SELECT * from courses where "No of lessons" > 9

TIP : Replace "No of lessons" with column name with numberic data type according to your dataset

3. To fetch the course information where Course Name is Python, so the WHERE clause with SELECT statement should look like

SELECT * from courses WHERE "Course Name" = 'Python'

TIP : Replace "Course Name" with column name according to your dataset. If the column data type is Text then enclose it in single quotes ('')

4. To fetch course information where Course Name start with SQL and does not matter what come after that, so the WHERE clause with SELECT statements should like this 

SELECT * from courses where "Course Name" like 'SQL%'

The output should like as follows:

5. To  fetch course information where the ID is greater than 2 OR the No of lessons < 10, so the WHERE clause with SELECT statements should like this 

SELECT * from courses where "ID" > 3 AND "No of lessons" < 10

The output should like as follows: 

In case only the records that satisfies both the criteria ID >3 and No of lessons <10 will be returend as AND Logical operator is used.

5. To  fetch course information where the ID is 4 or 6, so the WHERE clause with SELECT statements should like this 

SELECT * from courses where "ID" IN ( 4, 6)

The output should like this as follows .

6. To  fetch course information where the No of lessons is greater than or equal to 10 and less than or equal to 20 , so the WHERE clause with SELECT statements should like this 

SELECT * from courses where "No of lessons" WHERE "No of lessons" BETWEEN 10 AND 20

The output should like as follows :

6. To  fetch course information where the Course Name is not null , so the WHERE clause with SELECT statements should like this 

SELECT * from courses WHERE "Course Name" IS NOT NULL

The output should like as follows :

Let's practice to use WHERE clause with SELECT statement on Impact Cloud Dashboard

Login to Impact Cloud instance using the credentials. For this example, we will be using Impact Cloud Demo instance. 

Go to SQL Lab, Select SQL Editor as shown below:

1. Let us  fetch course information where the No of lessons is greater than or equal to 10 and less than or equal to 20 , so the query should be 

SELECT * FROM gc_demo.courses where "No of lessons" BETWEEN 10 AND 20

NOTE : gc_demo - is the schema name where Dataset courses resides.

To do this, write / copy paste the query on the SQL Editor as shown below. Click RUN to execute the query to display the results.

TO DO :

1. Execute all the various WHERE clause with search condition learnt above with SELECT Statement to get familiar with Impact Cloud.

2. Adapt the SELECT Statement with WHERE clause to suit your requirement.

Congratulations !! You have learnt to use SELECT Statement with WHERE clause.