SQL with Examples : SQL WHERE Clause

SQL WHERE Clause

Database tables usually contain large amounts of data, and you rarely need to retrieve all the rows in a table. More often than not, you will want to retrieve the subset of the table’s data as needed for specific operations or reports. Retrieving the desired data involves specifying search criteria, also known as search condition.

The data is filtered by specifying the search condition in the WHERE clause within the SELECT statement. If the condition is true for a row, then that row is included in the result set.

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition;

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.

This table is part of an ‘Employee Management System’ that contains basic information about employees.

ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 New York Developer 32000
3 Max 26 New York Janitor 9000
4 Kim 25 NULL Manager 55000
5 Joe 23 Chicago Developer 30000
6 Sam NULL Chicago Janitor 10000

Using the WHERE Clause

The WHERE clause allows you to retrieve only rows you are interested in.

For example, perhaps you are interested in retrieving data from the ‘Employees’ table, but only those employees whose salary is $30,000. The following query employs the WHERE clause to retrieve only one employee record:

SELECT *
FROM Employees
WHERE Salary = 30000;
ID Name Age City Job Salary
5 Joe 23 Chicago Developer 30000

The rules for qualifying text fields follow the same structure as number fields. However, when using text, you must wrap literals (or text values you specify) in single quotes.

For example, if you wanted to view employees from ‘New York’, you could run this query:

SELECT *
FROM Employees
WHERE City = 'New York';
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 New York Developer 32000
3 Max 26 New York Janitor 9000

The WHERE Clause Operators

The above examples test only for equality – determining if a column contains a specific value. The WHERE clause doesn’t always have to test for equality. You can use other comparison operators to filter down records.

SQL supports a whole range of conditional operators.

Operator Description Example
= Equal WHERE x = 1
!=, <> Not equal WHERE x <> ‘a’
> Greater than WHERE x > 1
< Less than WHERE x < 1
>= Greater than or equal WHERE x >= 1
<= Less than or equal WHERE x <= 1
BETWEEN Checks if a value lies within a range WHERE x BETWEEN 1 AND 10
EXISTS Checks if rows exist matching conditions that you specify WHERE EXISTS(subquery)
IN Checks if a value is contained in a set of specified values WHERE x IN (5, 10, 15, 20)
IS [NOT] NULL Tests for nullity WHERE x IS NULL
LIKE Checks if a value matches a pattern WHERE x LIKE ‘%abc_’

You have already seen an example of testing for equality. Let’s take a look at some examples that demonstrate the use of other operators.

The first example lists all employees whose salary is more than $40,000

SELECT *
FROM Employees
WHERE Salary > 40000;
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
4 Kim 25 NULL Manager 55000

The next example lists all employees who are not developers.

SELECT *
FROM Employees
WHERE Job <> 'Developer';
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
3 Max 26 New York Janitor 9000
4 Kim 25 NULL Manager 55000
6 Sam NULL Chicago Janitor 10000

Combining Conditions (with AND and OR operator)

Often, you’ll need to specify multiple conditions in a single WHERE clause, for example, to retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition.

The AND Operator

If two conditions are connected by the AND operator, rows are retrieved for which both conditions are true.

For example, if you wanted to select employees who are not ‘Developers’ and who live in ‘New York’ city, you could run this query:

SELECT *
FROM Employees
WHERE Job <> 'Developer' AND City = 'New York';
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
3 Max 26 New York Janitor 9000

The first condition Job <> 'Developer' filtered out 4 of 6 employee rows, and the second condition City = 'New York' filtered out an additional 2 rows, leaving 2 rows in the final result set.

The OR Operator

If two conditions are connected by the OR operator, all rows of a table are retrieved in which either the first or the second condition (or both) is true.

For example, here is a query that selects only those employees who are either ‘Managers’ or ‘Developers’

SELECT *
FROM Employees
WHERE Job = 'Manager' OR Job = 'Developer';
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 New York Developer 32000
4 Kim 25 NULL Manager 55000
5 Joe 23 Chicago Developer 30000

Negating a Condition (with NOT operator)

NOT operator has one and only one function: negating whatever condition comes next. It retrieves the rows for which the specified condition is FALSE (NOT TRUE).

The NOT operator is never used by itself; It is always used in conjunction with other operators such as BETWEEN, ANY, AND, OR, or LIKE. That’s why the NOT keyword is used before the column name, and not after it.

For example, if you wanted to select all employees who are not from ‘Chicago’, you could write a query like:

SELECT *
FROM Employees
WHERE NOT City = 'Chicago';
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 New York Developer 32000
3 Max 26 New York Janitor 9000

Range Conditions

Along with checking against a single value, you can build conditions that check for a range of values. This type of condition is used when working with numerical or temporal data (data related to date and time).

To check whether an expression falls within a certain range, you can use the BETWEEN operator. Its syntax differs slightly from other WHERE clause operators because it requires two values – the beginning and the end of the range.

The BETWEEN operator can be used, for example, to find all employees whose salaries range between $30,000 to $40,000.

SELECT *
FROM Employees
WHERE Salary BETWEEN 30000 and 40000;
ID Name Age City Job Salary
2 Eve 24 New York Developer 32000
5 Joe 23 Chicago Developer 30000

Read more about the BETWEEN operator here.

Membership Conditions

In some cases you might want to check for an expression, not against a single value or range of values, but against a set of values. For example, you might want to find all employees who are either 23, 26 or 28 years old.

While this can be done by OR’ing three conditions together which is not very tedious, imagine if the set of expressions contained 10 or 20 values. For these situations, you can use the IN operator instead:

SELECT *
FROM Employees
WHERE Age IN(23, 26, 28);
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
3 Max 26 New York Janitor 9000
5 Joe 23 Chicago Developer 30000

If you want to see whether the expression does not exist within a set of expressions, you can use the NOT IN operator.

For example, the following query lists all employees who are neither 23, 26 nor 28 years old.

SELECT *
FROM Employees
WHERE Age NOT IN(23, 26, 28);
ID Name Age City Job Salary
2 Eve 24 New York Developer 32000
4 Kim 25 NULL Manager 55000

Read more about the IN operator here.

Matching Conditions

By now, you have been introduced to conditions that identify an exact string or a set of strings; the final condition type deals with partial string matches. For example, you may want to find all employees whose second letter of name is ‘o’.

When searching for such partial string matches, the LIKE operator is used. There are two wildcards often used in conjunction with the LIKE operator:

%    The percent sign matches zero or more characters

_     The underscore character matches just a single character

As an example let’s find all employees whose second letter of name is ‘o’.

SELECT *
FROM Employees
WHERE Name LIKE '_o%';
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
5 Joe 23 Chicago Developer 30000

Read more about the LIKE operator here.

Checking for NULL Values

You may have noticed that some columns, such as ‘Age’ and ‘City’, have NULL values. A NULL is a value that has no value. It is nothing but an absence of value.

NULL values cannot be determined with an ‘= NULL’. You need to use the IS NULL or IS NOT NULL clauses to identify them. So, to get a list of employees with no recorded city, you could run this query:

SELECT *
FROM Employees
WHERE City IS NULL;
ID Name Age City Job Salary
4 Kim 25 NULL Manager 55000

 

Python Example for Beginners

Two Machine Learning Fields

There are two sides to machine learning:

  • Practical Machine Learning:This is about querying databases, cleaning data, writing scripts to transform data and gluing algorithm and libraries together and writing custom code to squeeze reliable answers from data to satisfy difficult and ill defined questions. It’s the mess of reality.
  • Theoretical Machine Learning: This is about math and abstraction and idealized scenarios and limits and beauty and informing what is possible. It is a whole lot neater and cleaner and removed from the mess of reality.

Data Science Resources: Data Science Recipes and Applied Machine Learning Recipes

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Recipes in Project-Based Learning:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

Comparing Different Machine Learning Algorithms in Python for Classification (FREE)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.