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.
SELECT column_name(s) FROM table_name WHERE condition;
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.
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;
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';
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.
|=||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;
The next example lists all employees who are not developers.
SELECT * FROM Employees WHERE Job <> 'Developer';
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';
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';
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';
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;
Read more about the BETWEEN operator here.
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);
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);
Read more about the IN operator here.
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%';
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;
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:
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.