SQL Basics 3: WHERE Statement
SQL
Discover the secrets of SQL's true power with our concise guide on the WHERE statement and functions. Learn how to filter data like a pro and perform transformative operations within your queries. Whether you're a beginner or an experienced data wrangler, this blog post will empower you to harness the full potential of SQL's core components. Join us on this enlightening journey and elevate your database querying skills today!
Hello everyone, this is the third post about SQL Basics.
If you missed the last 2 posts, and want to check them out to follow up with today’s exercise, go to:
SQL Basics 1: Create Table and Add Data in SSMS
There, we created 2 tables:
EmployeeDemographics
EmployeeSalary
We will be using these tables to go over WHERE statement examples today.
SQL Basics 2: SELECT + FROM Statements
Which we learned the basics of SELECT and FROM statements, that we will be constantly using for queries.
Now, let’s talk about WHERE statement in SQL.
Structured Query Language (SQL) is the backbone of managing and manipulating data in relational databases. One of the essential components of SQL is the WHERE statement, which allows developers to filter data with precision. In this blog post, we will dig into the details of the WHERE statement and explore its role in fetching specific information from databases.
Understanding the SQL WHERE Statement:
The WHERE statement is a fundamental clause in SQL that enables us to narrow down the results of a query by specifying conditions that must be met. It is used with the SELECT, UPDATE, DELETE, and other SQL commands to filter rows based on specified criteria.
Essentially, the WHERE clause acts as a gatekeeper, permitting only the data that fulfills certain conditions to be displayed or affected by the query.
The basic syntax of the WHERE statement is as follows:
SELECT: This keyword is used to indicate what you want to retrieve data from the database.
column1, column2, ...: These are the names of the columns you want to retrieve data from. You can specify multiple column names separated by commas, or you can use the asterisk (*) to select all columns in the table.
FROM: This keyword is used to indicate that you are specifying the source table from which you want to retrieve data.
table_name: This is the name of the table where your data resides. You need to replace "table_name" with the actual name of the table you want to query.
WHERE: A condition that limits the amount of data and specify what data you want to return.
Using Operators in the WHERE Statement in SQL:
SQL offers a variety of operators that can be used in conjunction with the WHERE statement to create meaningful conditions. Some commonly used operators include:
1. Comparison Operators:
These operators are used to compare values. They include =, <, >, <=, >=, and <> (not equal).
For example, we want to find the first name “Brad” from our EmlpoyeeDemographics table. So, we use the query “SELECT * FROM EmployeeDemographics WHERE FirstName = ‘Brad’" -> Execute
Only Brad comes up on the table.
Now, let’s say we want all employees but Brad. “SELECT * FROM EmployeeDemographics WHERE FirstName <> ‘Brad’" -> Execute
Everyone other than Brad comes as result.
To compare greater than and less than, let’s use Age instead of FirstName.
We want age greater than 55. “SELECT * FROM EmployeeDemographics WHERE Age > 54" -> Execute
Everyone over 54 is shown in the table. Note that we are not including age 54.
If we want to include the age 54, we include the equal sign. “SELECT * FROM EmployeeDemographics WHERE Age >= 54" -> Execute
In this case, Jennifer Wilson is included to the result table, as she is 54.
The same we can do with less than 54. “SELECT * FROM EmployeeDemographics WHERE Age < 54" -> Execute
Everyone under 54 is in the table. And, we can include the age of 54 but adding the equal sign to the query.
2. Logical Operators:
Logical operators like "AND," "OR," and "NOT" are used to combine multiple conditions in a query.
For this example, we will use two criteria to specify what we need.
We want employees that are under or equal to 54 and they are male. “SELECT * FROM EmployeeDemographics WHERE Age <= 54 AND Gender = ‘Male’"-> Execute
The result shows only Christian Lopez, who is under 54 and is male.
If we use OR instead of AND, only one of these criteria must be correct for it to meet. “SELECT * FROM EmployeeDemographics WHERE Age <= 54 OR Gender = ‘Male’" -> Execute
The result is a list of employees that are under or equal to the age 54 OR their gender equals male. If you look the list, Brad is over 54 years old, however, he is male. Bringing him to the result list.
The NOT function is used to negate a condition. “SELECT * FROM EmployeeDemographics WHERE NOT Age <= 54" -> Execute
The result brings employees that are not ages 54 and under, including both genders, however, in this specific table, only male is over 54 years old.
3. IN Operator:
The "IN" operator allows us to specify multiple values in a list, and the query will return rows where the specified column's value matches any of the values in the list. Similar to equal statement but it is multiple equal statements.
Instead of writing “SELECT * FROM EmployeeDemographics WHERE FirstName = ‘Brad’ OR FirstName = ‘Adam’ OR FirstName = ‘Tom’” -> Execute
We can get the same result just by querying, “SELECT * FROM EmployeeDemographics WHERE FirstName IN (‘Brad’, ‘Adam’, ‘Tom’)" -> Execute
4. BETWEEN Operator:
The "BETWEEN" operator is used to filter data within a range of values.
For this example, we want employees with ages between 40 and 55. “SELECT * FROM EmployeeDemographics Age BETWEEN 40 AND 55" -> Execute
NOTE we are not including ages 40 and 55. If it was the case, we would add = before both ages.
5. LIKE Operator:
The "LIKE" operator is used to perform pattern matching with the help of wildcard characters such as "%" (matches any sequence of characters) and "_" (matches any single character).
Let’s say we want a list of employees that the first name starts with the letter A. “SELECT * FROM EmployeeDemographics WHERE FirstName LIKE ‘A%’ "-> Execute
The result is Angelina and Adam, as their both names start with the letter A. The %, also known as the wildcard, means that after the letter A, can be any other letters.
If we insert the wildcard in front and at the end of the letter “A”, means that any name that contains the letter “A” in it, will return in the result. “SELECT * FROM EmployeeDemographics WHERE FirstName LIKE ‘%A%’ -> Execute
In this case, all names that contain “A” in it, not mattering if it is in the beginning, middle and/or at the end will return.
Now, let’s say we want a first name that starts with “A” and have “I” anywhere after the first letter. “SELECT * FROM EmployeeDemographics WHERE FirstName LIKE A%I%” -> Execute
We can also say we want a first name that ends with the letter “M”. “SELECT * FROM EmployeeDemographics WHERE FirstName LIKE %M” -> Execute
Or even put all together saying a first name that starts with letter “J”, have “I” somewhere, and ends with “R”. “SELECT * FROM EmployeeDemographics WHERE FirstName LIKE ‘J%I%R” -> Execute
The underscore character “_” is a wildcard used in SQL’s LIKE operator for pattern matching. But it represents a single character.
For example, “SELECT * FROM EmployeeDemographics WHERE LastName LIKE ‘_E_’” -> Execute
Different from “%” that can be used to represent zero, one, or more characters that can be placed at the beginning, end, or in the middle of a pattern in a string, “_” represents a single character, resulting names that have exactly three characters.
Confused? Check out
SQL Wildcards: The Difference Between "%" and "_" to clarify your doubts.
As we can see, we can play around with the wildcards as needed. Just pay attention that the letters must be in order. It will not bring as result Jennifer if letters are placed LIKE ‘J%I%N%R’. The correct way would be ‘J%N%I%R’.
There are many ways to use the wildcards, especially with numerical data, but for today, we are keeping it simple.
6. NULL, NOT NULL
They are used to check for the presence or absence of a value in a column. For our tables, we do not have NULL fields for now (I will work on it in a different post). But, just for fun…
“SELECT * FROM EmployeeDemographics WHERE FirstName is NULL -> Execute
As we already know, none of the first names are NULL.
“SELECT * FROM EmployeeDemographics WHERE FirstName is NOT NULL -> Execute
And all first names are NOT NULL.
NOTE: NULL and NOT NULL are very useful in many situations especially when you are working with large, missing data, but for this simple demonstration purpose of today’s post, that’s all they do.
The WHERE statement in SQL is a powerful tool that allows us to filter and retrieve data based on specific conditions. By combining different operators, we can create complex queries to precisely extract the information we need from a database.
Understanding and utilizing the "WHERE" statement effectively is essential for developers and data analysts to make the most out of their SQL queries and efficiently manage data.
Remembering that in this post we showed some simple ways to use WHERE and a few useful functions. As we progress, more advanced examples will be applied.
Keep on learning and implementing because knowledge is powerful when it is applied!
If you like this content and want to learn more, you might also read:
SQL Fundamentals for Data Analysis: A Hands-On Learning Series
SQL Basics 1: How to Create Table and Add Data in SSMS
SQL Basics 2: SELECT + FROM Statements
SQL Basics 4: GROUP BY and ORDER BY Statements
SQL Intermediate 1: INNER and OUTER Joins
SQL Intermediate 2: UNION Operator
SQL Intermediate 3: CASE Statement
SQL Intermediate 4: HAVING Clause
SQL Intermediate 5: UPDATE and DELETE Data
SQL Intermediate 6: AS - Aliasing
SQL Intermediate 7: PARTITION BY Clause
SQL Advanced 1: CTEs - Common Table Expressions
SQL Advanced 3: Stored Procedures
SQL Advanced 4: SUBQUERIES - In Select, From and Where
SQL Advanced 5: String Functions - TRIM, LTRIM, RTRIM, REPLACE, SUBSTRING, UPPER, and LOWER