Filtering SELECT query result sets is a common MySQL operation. This blog post will visit basic usage of the WHERE clause for just such a purpose.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.
About Where
The WHERE clause is used to filter results by specifying a filter condition or selection criteria.
Usage is not limited to just SELECT queries, as this clause is also used with the UPDATE and DELETE DML commands. However, we will concentrate on its usage for criteria filtering with SELECT queries in this blog post.
Photo by Anthony Martino on Unsplash
Syntax and Operators
The WHERE clause has the following syntax:
WHERE boolean_expression_conditional;
Selection criteria comparison is performed with any of one or more of the below operators:
<: Less than.>: Greater than.< =: Less than or equals to.>=: Greater than or equals to.=: Equals to.!=: Not equals to.<>: Not equals to (alternate syntax).IN(): Comparison function used for checking membership against a list of values or expressions.NOT IN(): Comparison function used for checking membership not found in a list of values or expressions.
Multiple conditionals can be specified in the WHERE clause using either the AND or OR logical operators as in:
AND
boolean_expression_two;
In the above scenario, both boolean expression conditionals must be true for a row (or rows) to make the final result set returned. (Note: This example shows only two filter conditions. More can be specified if desired.)
OR
boolean_expression_two;
In this scenario, a row (or rows) is included in the final result set when either of the boolean expressions returns true.
Enough of the boring boilerplate yadda yadda. Let’s see some example uses of the WHERE clause.
Below is the description of the employees table from a mock HR database I will be using for many of these examples:
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| EMPLOYEE_ID | decimal(6,0) | NO | PRI | 0 | |
| FIRST_NAME | varchar(20) | YES | | NULL | |
| LAST_NAME | varchar(25) | NO | MUL | NULL | |
| EMAIL | varchar(25) | NO | UNI | NULL | |
| PHONE_NUMBER | varchar(20) | YES | | NULL | |
| HIRE_DATE | date | NO | | NULL | |
| JOB_ID | varchar(10) | NO | MUL | NULL | |
| SALARY | decimal(8,2) | YES | | NULL | |
| COMMISSION_PCT | decimal(2,2) | YES | | NULL | |
| MANAGER_ID | decimal(6,0) | YES | MUL | NULL | |
| DEPARTMENT_ID | decimal(4,0) | YES | MUL | NULL | |
+----------------+--------------+------+-----+---------+-------+
11 rows in set (0.05 sec)
Let’s have a look at all the available jobs in the employees table,
+------------+
| JOB_ID |
+------------+
| AC_ACCOUNT |
| AC_MGR |
| AD_ASST |
| AD_PRES |
| AD_VP |
| FI_ACCOUNT |
| FI_MGR |
| HR_REP |
| IT_PROG |
| MK_MAN |
| MK_REP |
| PR_REP |
| PU_CLERK |
| PU_MAN |
| SA_MAN |
| SA_REP |
| SH_CLERK |
| ST_CLERK |
| ST_MAN |
+------------+
Single Conditionals
How about we retrieve the FIRST_NAME and LAST_NAMEof everyone who has the IT_PROG JOB_ID?
-> FROM employees
-> WHERE JOB_ID = 'IT_PROG';
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| Alexander | Hunold |
| Bruce | Ernst |
| David | Austin |
| Valli | Pataballa |
| Diana | Lorentz |
+------------+-----------+
5 rows in set (0.04 sec)
By specifying JOB_ID = 'IT_PROG'; in the WHERE clause, the above SELECT query is being filtered for only those records that have the JOB_ID column value of IT_PROG.
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Multiple Conditionals
More than one conditional can be provided to the WHERE clause.
Below the AND logical operator is used to specify two boolean expression conditionals in the WHERE clause.
Both conditionals must result to true, before any record(s) is included in the final result set.
-> FROM employees
-> WHERE JOB_ID = 'IT_PROG'
-> AND SALARY < 5000;
+------------+-----------+---------+
| FIRST_NAME | LAST_NAME | SALARY |
+------------+-----------+---------+
| David | Austin | 4800.00 |
| Valli | Pataballa | 4800.00 |
| Diana | Lorentz | 4200.00 |
+------------+-----------+---------+
3 rows in set (0.00 sec)
The two conditionals checked in this WHERE clause are,
-
JOB_ID = 'IT_PROG'SALARY < 5000
which is specifying that anyone working in the 'IT_PROG' job role, along with a SALARY less than 5000, is included in the returned results.
OR is another logical operator often used. Using this operator, when either of the specified boolean expression conditionals returns as true, that record (or records) will be included in the final result set.
-> FROM employees
-> WHERE JOB_ID = 'IT_PROG'
-> OR
-> JOB_ID = 'FI_ACCOUNT';
+-------------+-----------+------------+
| FIRST_NAME | LAST_NAME | JOB_ID |
+-------------+-----------+------------+
| Daniel | Faviet | FI_ACCOUNT |
| John | Chen | FI_ACCOUNT |
| Ismael | Sciarra | FI_ACCOUNT |
| Jose Manuel | Urman | FI_ACCOUNT |
| Luis | Popp | FI_ACCOUNT |
| Alexander | Hunold | IT_PROG |
| Bruce | Ernst | IT_PROG |
| David | Austin | IT_PROG |
| Valli | Pataballa | IT_PROG |
| Diana | Lorentz | IT_PROG |
+-------------+-----------+------------+
10 rows in set (0.00 sec)
With this query, for a record to make the final result set, the JOB_ID can be either IT_PROG or FI_ACCOUNT.
Checking For Membership
The comparison functions IN() and NOT IN() check the specified value in the WHERE clause for membership found or not found, in a list of values or expressions.
Let’s look at another example table in this database, the jobs table.
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| JOB_ID | varchar(10) | NO | PRI | | |
| JOB_TITLE | varchar(35) | NO | | NULL | |
| MIN_SALARY | decimal(6,0) | YES | | NULL | |
| MAX_SALARY | decimal(6,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Suppose we know first hand we want to see the MAX_SALARY for certain job roles.
Lets’ get an idea of the MAX_SALARY for the positions:Accountant, Stock Clerk, and Programmer.
-> FROM jobs
-> WHERE JOB_TITLE IN ('Accountant','Stock Clerk','Programmer');
+-------------+------------+
| JOB_TITLE | MAX_SALARY |
+-------------+------------+
| Accountant | 9000 |
| Stock Clerk | 5000 |
| Programmer | 10000 |
+-------------+------------+
3 rows in set (0.00 sec)
Providing the list, ('Accountant','Stock Clerk','Programmer'), the JOB_TITLE column specified in the WHERE clause is checked against these values and returns that row, if any members of that list of values is present for that column.
That same query can be run using multiple OR comparisons as well:
-> FROM jobs
-> WHERE JOB_TITLE = 'Accountant'
-> OR JOB_TITLE = 'Stock Clerk'
-> OR JOB_TITLE = 'Programmer';
+-------------+------------+
| JOB_TITLE | MAX_SALARY |
+-------------+------------+
| Accountant | 9000 |
| Stock Clerk | 5000 |
| Programmer | 10000 |
+-------------+------------+
3 rows in set (0.03 sec)
Conversely, NOT IN() can be used to see what all the other positions besides 'Accountant', 'Stock Clerk', and 'Programmer' bring in for MAX_SALARY:
-> FROM jobs
-> WHERE JOB_TITLE NOT IN ('Accountant','Stock Clerk','Programmer');
+---------------------------------+------------+
| JOB_TITLE | MAX_SALARY |
+---------------------------------+------------+
| President | 40000 |
| Administration Vice President | 30000 |
| Administration Assistant | 6000 |
| Finance Manager | 16000 |
| Accounting Manager | 16000 |
| Public Accountant | 9000 |
| Sales Manager | 20000 |
| Sales Representative | 12000 |
| Purchasing Manager | 15000 |
| Purchasing Clerk | 5500 |
| Stock Manager | 8500 |
| Shipping Clerk | 5500 |
| Marketing Manager | 15000 |
| Marketing Representative | 9000 |
| Human Resources Representative | 9000 |
| Public Relations Representative | 10500 |
+---------------------------------+------------+
16 rows in set (0.04 sec)
This query returns results for JOB_ID columns that are not an 'Accountant' ,'Stock Clerk' , or'Programmer'.
Summarizing Where
WHERE clauses are responsible for returning just what you want from a query result. Finding its usage in many of the DML commands: SELECT, UPDATE, and DELETE, it is one of the most used clauses and in my opinion, one worth getting to know well.
Explore the official MySQL 5.7 Online Manual for study, questions, and clarification.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
How to Select from multiple tables without joins or any common value ?
Hi Akash. Thank you for your comment. Not sure I understand completely but you can use UNION to select from multiple sources so long as the same number and type of columns are listed in each SELECT clause. Hope that helps. Thanks