Specifying the Filter Criteria

Learn how to filter the record using the WHERE clause.

We'll cover the following

Sifting results

There are instances when we specifically want to observe data that meets particular criteria. For example, we might be interested in viewing sales that surpass a designated amount, like over 200. Is it possible to accomplish this, and if so, how? Let’s try to understand.

Recall the three questions or clauses that constitute the core of an SQL query. These include:

  1. What are the fields/attributes we want to show in our result set? 

  2. What are the data sources? 

  3. What criteria/conditions are to be met for those results? 

The third clause deals with the filtering criteria. Successfully achieving our goal hinges on correctly crafting this third clause. We previously discussed the first two; let’s explore the third.

Q

The list of all the keywords we have used so far in this course is given below; choose the most suitable keyword for specifying the filtering criteria.

A)

SELECT

B)

DISTINCT

C)

WHERE

D)

FROM

The WHERE clause

The general structure for an SQL query containing a WHERE clause is outlined as follows:

SELECT column1, column2, ... 
FROM table_name 
WHERE condition;

The essence lies in defining the condition. We’ll begin with straightforward conditions that involve comparison operators. The general syntax of a comparison operator in the SQL WHERE clause is as follows:

Press + to interact
Minimum query structure
Minimum query structure

The specification of the condition in the WHERE clause involves using a comparison operator structured as WHERE column_name operator value. These operators encompass (=, <> or !=, >, <, >=, <=), each signifying a unique relationship, as mentioned below:

  • Equal: =

  • Not equal: <> or !=

  • Greater than: >

  • Less than: <

  • Greater than or equal to: >=

  • Less than or equal to: <=

We can employ either <> or != for conducting inequality tests between two expressions. Both operators yield identical results. The only distinction lies in adherence to standards; <> aligns with the ISO standard, whereas != does not. It is advised to use the <> operator for compatibility with the ISO standard.

Note: When we compare values in SQL, we use =. It is similar to how == works in languages like Java to check for equality. However, it's important to note that in such languages, = assigns values to variables, while in SQL, there isn't any assignment using =. SQL exclusively employs = for comparison and equality checks, without any assignment functionality tied to this symbol.

Let’s initiate our first query with a WHERE clause. We’ll begin by generating a list of sales that exceed 200.

Well done! You’ve managed to complete a query using all three fundamental clauses.

What if we wanted this list to include sales of 200 or above? Can you do it?

Now, rewrite the query to include sales of 200 or above.

Press + to interact
-- Write your query here.