Master the powerful WHERE clause to filter SQL results and find exactly the data you need
Welcome to one of SQL's most powerful features: the WHERE clause! If SELECT determines which columns you get, WHERE determines which rows make it into your result set.
Think of the WHERE clause as your data detective tool. It lets you ask precise questions like "Who are our customers in New York?" or "Which products cost more than $100?" or "Which orders were placed between January and March?" Without the WHERE clause, these questions would be nearly impossible to answer efficiently.
In this lesson, we'll dive deep into filtering data with the WHERE clause. You'll learn how to craft precise conditions using comparison operators, combine multiple conditions with logical operators, and use specialized operators to handle common filtering scenarios.
By the end of this lesson, you'll be able to filter SQL data with surgical precision!
The WHERE clause appears after the FROM clause in a SELECT statement and filters rows based on a specified condition:
1SELECT column1, column2, ...
2FROM table_name
3WHERE condition;Only rows that satisfy the condition (where the condition evaluates to TRUE) will be included in the results.
Remember the SQL execution order we learned in the previous lesson:
The WHERE clause is applied early in the execution process—immediately after identifying the source table. This is important for performance reasons, as it reduces the number of rows processed by later steps in the query.
Let's see how a simple WHERE clause affects our results:
Without WHERE:
1SELECT employee_id, first_name, last_name, department, salary
2FROM employees;
3-- Returns ALL employees (e.g., 1000 rows)With WHERE:
1SELECT employee_id, first_name, last_name, department, salary
2FROM employees
3WHERE department = 'Marketing';
4-- Returns ONLY marketing employees (e.g., 50 rows)This simple addition drastically reduces our result set from potentially thousands of rows to just the ones we need.
Comparison operators are the building blocks of WHERE clause conditions. They compare values and return a boolean result (TRUE or FALSE).
| Operator | Description | Example |
|----------|-----------------------|----------------------------------------|
| = | Equal to | WHERE salary = 50000 |
| > | Greater than | WHERE salary > 50000 |
| < | Less than | WHERE salary < 50000 |
| >= | Greater than or equal | WHERE salary >= 50000 |
| <= | Less than or equal | WHERE salary <= 50000 |
| != | Not equal to | WHERE salary != 50000 |
| <> | Not equal to | WHERE salary <> 50000 (alternative)|
1-- Find employees with salary equal to 75000
2SELECT * FROM employees WHERE salary = 75000;
3
4-- Find employees with salary greater than 75000
5SELECT * FROM employees WHERE salary > 75000;
6
7-- Find employees with salary less than 75000
8SELECT * FROM employees WHERE salary < 75000;
9
10-- Find employees with salary greater than or equal to 75000
11SELECT * FROM employees WHERE salary >= 75000;
12
13-- Find employees with salary less than or equal to 75000
14SELECT * FROM employees WHERE salary <= 75000;
15
16-- Find employees with salary not equal to 75000
17SELECT * FROM employees WHERE salary != 75000;
18-- OR
19SELECT * FROM employees WHERE salary <> 75000;When comparing strings, remember:
1-- Find employees in the Marketing department
2SELECT * FROM employees WHERE department = 'Marketing';
3
4-- Find employees whose last name starts with 'S' (this is case-sensitive)
5SELECT * FROM employees WHERE last_name >= 'S' AND last_name < 'T';Date comparisons allow you to filter records based on time:
1-- Find employees hired after January 1, 2021
2SELECT * FROM employees WHERE hire_date > '2021-01-01';
3
4-- Find employees hired in 2020
5SELECT * FROM employees WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';Date format depends on your database. ISO format (YYYY-MM-DD) is most portable.
Logical operators allow you to combine multiple conditions in your WHERE clause.
The AND operator returns TRUE only if both conditions are TRUE:
1SELECT *
2FROM employees
3WHERE department = 'Engineering' AND salary > 90000;This query finds employees who are in the Engineering department AND have a salary greater than $90,000. Both conditions must be true for a row to be included in the results.
The OR operator returns TRUE if either condition is TRUE:
1SELECT *
2FROM employees
3WHERE department = 'Engineering' OR department = 'Marketing';This query finds employees who are in the Engineering department OR in the Marketing department. If either condition is true, the row will be included.
The NOT operator negates a condition:
1SELECT *
2FROM employees
3WHERE NOT department = 'Engineering';
4-- Same as: WHERE department != 'Engineering'This query finds employees who are NOT in the Engineering department.
You can combine multiple conditions with AND, OR, and NOT. When doing so, be aware of operator precedence:
1SELECT *
2FROM employees
3WHERE (department = 'Engineering' OR department = 'Marketing')
4 AND salary > 80000;This query finds employees who are either in Engineering OR Marketing, AND who earn more than $80,000.
Without parentheses, the query would be interpreted differently due to operator precedence:
1SELECT *
2FROM employees
3WHERE department = 'Engineering' OR department = 'Marketing'
4 AND salary > 80000;This would find all Engineering employees (regardless of salary) OR Marketing employees who earn more than $80,000.
When filtering on multiple criteria that all must be true, you can chain multiple AND conditions:
1SELECT *
2FROM employees
3WHERE department = 'Engineering'
4 AND salary > 80000
5 AND hire_date > '2020-01-01'
6 AND manager_id = 101;This approach provides a very specific filter that only returns rows matching all criteria.
The IN and BETWEEN operators provide convenient shortcuts for common filtering patterns.
The IN operator checks if a value matches any value in a list:
1SELECT *
2FROM employees
3WHERE department IN ('Engineering', 'Marketing', 'Sales');This is equivalent to:
1SELECT *
2FROM employees
3WHERE department = 'Engineering' OR department = 'Marketing' OR department = 'Sales';The IN operator is more readable and often more efficient, especially for longer lists of values.
You can also use the NOT IN operator to exclude rows:
1SELECT *
2FROM employees
3WHERE department NOT IN ('HR', 'Finance');This finds employees who are not in the HR or Finance departments.
The BETWEEN operator checks if a value falls within a specified range (inclusive):
1SELECT *
2FROM employees
3WHERE salary BETWEEN 50000 AND 75000;This is equivalent to:
1SELECT *
2FROM employees
3WHERE salary >= 50000 AND salary <= 75000;The BETWEEN operator works with numbers, dates, and strings:
1-- Date range
2SELECT *
3FROM employees
4WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';
5
6-- String range (alphabetical)
7SELECT *
8FROM employees
9WHERE last_name BETWEEN 'A' AND 'M';You can also use NOT BETWEEN to exclude ranges:
1SELECT *
2FROM employees
3WHERE salary NOT BETWEEN 50000 AND 75000;This finds employees with salaries less than 50000 or greater than 75000.
The LIKE operator performs pattern matching using wildcards, which is invaluable for searching text data.
There are two primary wildcards used with LIKE:
1-- Names starting with 'J'
2SELECT * FROM employees WHERE first_name LIKE 'J%';
3
4-- Names ending with 'son'
5SELECT * FROM employees WHERE last_name LIKE '%son';
6
7-- Names containing 'ana' anywhere
8SELECT * FROM employees WHERE first_name LIKE '%ana%';
9
10-- Names with exactly 5 characters
11SELECT * FROM employees WHERE first_name LIKE '_____';
12
13-- Names with 'a' as the second character
14SELECT * FROM employees WHERE first_name LIKE '_a%';
15
16-- Email addresses from gmail.com
17SELECT * FROM employees WHERE email LIKE '%@gmail.com';You can also use NOT LIKE to find rows that don't match a pattern:
1-- Find employees whose names don't start with 'J'
2SELECT * FROM employees WHERE first_name NOT LIKE 'J%';By default, LIKE is case-sensitive in most database systems:
1-- In PostgreSQL/SQLite, this won't match '[email protected]'
2SELECT * FROM employees WHERE email LIKE '%@GMAIL.COM';To make LIKE case-insensitive:
1-- PostgreSQL (case-insensitive)
2SELECT * FROM employees WHERE email ILIKE '%@gmail.com';
3
4-- SQLite (case-insensitive)
5SELECT * FROM employees WHERE email LIKE '%@gmail.com' COLLATE NOCASE;If you need to search for the actual % or _ character, use the escape character:
1-- Find rows containing percentage signs
2SELECT * FROM products WHERE description LIKE '%10\%%' ESCAPE '\';
3
4-- Find rows containing underscore
5SELECT * FROM table_name WHERE column_name LIKE '%\_text%' ESCAPE '\';The escape character (here \) indicates that the following special character should be treated as a literal rather than a wildcard.
NULL in SQL represents missing or unknown values. Handling NULL requires special care because NULL is not a value—it represents the absence of a value.
The most important thing to understand about NULL:
To check if a value is NULL, use the IS NULL operator:
1-- Find employees with no manager
2SELECT * FROM employees WHERE manager_id IS NULL;To check if a value is not NULL, use IS NOT NULL:
1-- Find employees who have a manager
2SELECT * FROM employees WHERE manager_id IS NOT NULL;One of the most common mistakes in SQL is trying to check for NULL using the equals operator:
1-- INCORRECT: Will not find NULL values
2SELECT * FROM employees WHERE manager_id = NULL;
3
4-- CORRECT: Use IS NULL
5SELECT * FROM employees WHERE manager_id IS NULL;The first query will never return any results because NULL = NULL always evaluates to NULL (not TRUE).
The IN operator behaves predictably with NULL:
1-- This won't return rows where department is NULL
2SELECT * FROM employees WHERE department IN ('HR', 'Finance');But NOT IN behaves in a way that often surprises beginners:
1-- This will NOT return rows where department is NULL
2SELECT * FROM employees WHERE department NOT IN ('HR', 'Finance');This is because NULL NOT IN (anything) evaluates to NULL, not TRUE. To include NULL values when using NOT IN:
1SELECT * FROM employees
2WHERE department NOT IN ('HR', 'Finance')
3 OR department IS NULL;When NULL is used in expressions, the result is NULL:
1-- If commission_pct is NULL, this returns NULL
2SELECT salary * commission_pct FROM employees;To handle this, use the COALESCE function (available in most databases):
1-- Use 0 when commission_pct is NULL
2SELECT salary * COALESCE(commission_pct, 0) FROM employees;Remember that NULL in a logical expression evaluates to NULL, not TRUE or FALSE:
1-- This filter won't include rows where end_date is NULL
2SELECT * FROM projects WHERE end_date > '2022-01-01';To include NULL values, explicitly check for them:
1SELECT * FROM projects
2WHERE end_date > '2022-01-01' OR end_date IS NULL;Practice using various WHERE conditions to filter data in progressively more sophisticated ways.
1<!DOCTYPE html>
2<html lang="en">
3<head>
4 <meta charset="UTF-8">
5 <meta name="viewport" content="width=device-width, initial-scale=1.0">
6 <title>Filtering Data with WHERE</title>
7 <style>
8 body {
9 font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
10 line-height: 1.6;
11 color: #333;
12 max-width: 1000px;
13 margin: 0 auto;
14 padding: 20px;
15 background: linear-gradient(to right, #f5f7fa, #e9ecef);
16 }
17
18 .container {
19 background: white;
20 border-radius: 8px;
21 padding: 30px;
22 box-shadow: 0 4px 15px rgba(0,0,0,0.1);
23 }
24
25 h1 {
26 color: #3a5dd1;
27 margin-bottom: 30px;
28 }
29
30 h2 {
31 color: #2d4bb5;
32 margin-top: 40px;
33 padding-bottom: 10px;
34 border-bottom: 2px solid #e9ecef;
35 }
36
37 .example {
38 background: #f8f9fa;
39 border-left: 4px solid #3a5dd1;
40 padding: 20px;
41 margin: 25px 0;
42 border-radius: 0 8px 8px 0;
43 }
44
45 .query-box {
46 background: #2b2d42;
47 color: #edf2f4;
48 padding: 15px;
49 border-radius: 8px;
50 font-family: 'Courier New', monospace;
51 margin: 15px 0;
52 overflow-x: auto;
53 }
54
55 .query-box .keyword {
56 color: #ffb703;
57 font-weight: bold;
58 }
59
60 .query-box .string {
61 color: #8ac926;
62 }
63
64 .query-box .number {
65 color: #ff9e00;
66 }
67
68 .query-box .operator {
69 color: #fb8500;
70 }
71
72 .query-box .comment {
73 color: #8d99ae;
74 font-style: italic;
75 }
76
77 .result-table {
78 width: 100%;
79 border-collapse: collapse;
80 margin: 20px 0;
81 box-shadow: 0 2px 8px rgba(0,0,0,0.1);
82 }
83
84 .result-table th {
85 background: #3a5dd1;
86 color: white;
87 padding: 12px 15px;
88 text-align: left;
89 }
90
91 .result-table tr:nth-child(even) {
92 background: #f8f9fa;
93 }
94
95 .result-table td {
96 padding: 12px 15px;
97 border-bottom: 1px solid #e9ecef;
98 }
99
100 .note {
101 background: #e0fbec;
102 border-left: 4px solid #38b000;
103 padding: 15px;
104 margin: 20px 0;
105 border-radius: 0 8px 8px 0;
106 }
107
108 .note strong {
109 color: #38b000;
110 }
111
112 .warning {
113 background: #fff0db;
114 border-left: 4px solid #ff7200;
115 padding: 15px;
116 margin: 20px 0;
117 border-radius: 0 8px 8px 0;
118 }
119
120 .warning strong {
121 color: #ff7200;
122 }
123
124 .challenge {
125 background: #ffebfd;
126 border-left: 4px solid #9d4edd;
127 padding: 15px;
128 margin: 30px 0;
129 border-radius: 0 8px 8px 0;
130 }
131 </style>
132</head>
133<body>
134 <div class="container">
135 <h1>Mastering Data Filtering with the WHERE Clause</h1>
136 <p>Let's explore how to filter data with the WHERE clause, from simple conditions to complex combinations.</p>
137
138 <h2>1. Basic Comparison Operators</h2>
139 <div class="example">
140 <p>Start with simple numeric comparisons:</p>
141
142 <div class="query-box">
143 <span class="comment">-- Find employees with salaries above $75,000</span><br>
144 <span class="keyword">SELECT</span> employee_id, first_name, last_name, salary<br>
145 <span class="keyword">FROM</span> employees<br>
146 <span class="keyword">WHERE</span> salary <span class="operator">></span> <span class="number">75000</span>;
147 </div>
148
149 <div class="result-table">
150 <table>
151 <thead>
152 <tr>
153 <th>employee_id</th>
154 <th>first_name</th>
155 <th>last_name</th>
156 <th>salary</th>
157 </tr>
158 </thead>
159 <tbody>
160 <tr>
161 <td>101</td>
162 <td>John</td>
163 <td>Smith</td>
164 <td>85000</td>
165 </tr>
166 <tr>
167 <td>102</td>
168 <td>Maria</td>
169 <td>Garcia</td>
170 <td>92000</td>
171 </tr>
172 <tr>
173 <td>108</td>
174 <td>Sarah</td>
175 <td>Johnson</td>
176 <td>105000</td>
177 </tr>
178 </tbody>
179 </table>
180 </div>
181
182 <p>String comparisons work similarly:</p>
183
184 <div class="query-box">
185 <span class="comment">-- Find employees in the Engineering department</span><br>
186 <span class="keyword">SELECT</span> employee_id, first_name, last_name, department<br>
187 <span class="keyword">FROM</span> employees<br>
188 <span class="keyword">WHERE</span> department <span class="operator">=</span> <span class="string">'Engineering'</span>;
189 </div>
190
191 <div class="result-table">
192 <table>
193 <thead>
194 <tr>
195 <th>employee_id</th>
196 <th>first_name</th>
197 <th>last_name</th>
198 <th>department</th>
199 </tr>
200 </thead>
201 <tbody>
202 <tr>
203 <td>101</td>
204 <td>John</td>
205 <td>Smith</td>
206 <td>Engineering</td>
207 </tr>
208 <tr>
209 <td>104</td>
210 <td>Robert</td>
211 <td>Chen</td>
212 <td>Engineering</td>
213 </tr>
214 <tr>
215 <td>107</td>
216 <td>David</td>
217 <td>Wilson</td>
218 <td>Engineering</td>
219 </tr>
220 </tbody>
221 </table>
222 </div>
223 </div>
224
225 <h2>2. Using Logical Operators</h2>
226 <div class="example">
227 <p>Combine conditions with AND, OR, and NOT:</p>
228
229 <div class="query-box">
230 <span class="comment">-- Find Engineering employees with salaries above $70,000</span><br>
231 <span class="keyword">SELECT</span> employee_id, first_name, last_name, department, salary<br>
232 <span class="keyword">FROM</span> employees<br>
233 <span class="keyword">WHERE</span> department <span class="operator">=</span> <span class="string">'Engineering'</span><br>
234 <span class="operator">AND</span> salary <span class="operator">></span> <span class="number">70000</span>;
235 </div>
236
237 <div class="result-table">
238 <table>
239 <thead>
240 <tr>
241 <th>employee_id</th>
242 <th>first_name</th>
243 <th>last_name</th>
244 <th>department</th>
245 <th>salary</th>
246 </tr>
247 </thead>
248 <tbody>
249 <tr>
250 <td>101</td>
251 <td>John</td>
252 <td>Smith</td>
253 <td>Engineering</td>
254 <td>85000</td>
255 </tr>
256 </tbody>
257 </table>
258 </div>
259
260 <div class="query-box">
261 <span class="comment">-- Find employees in either Engineering OR Marketing</span><br>
262 <span class="keyword">SELECT</span> employee_id, first_name, last_name, department<br>
263 <span class="keyword">FROM</span> employees<br>
264 <span class="keyword">WHERE</span> department <span class="operator">=</span> <span class="string">'Engineering'</span><br>
265 <span class="operator">OR</span> department <span class="operator">=</span> <span class="string">'Marketing'</span>;
266 </div>
267
268 <div class="result-table">
269 <table>
270 <thead>
271 <tr>
272 <th>employee_id</th>
273 <th>first_name</th>
274 <th>last_name</th>
275 <th>department</th>
276 </tr>
277 </thead>
278 <tbody>
279 <tr>
280 <td>101</td>
281 <td>John</td>
282 <td>Smith</td>
283 <td>Engineering</td>
284 </tr>
285 <tr>
286 <td>102</td>
287 <td>Maria</td>
288 <td>Garcia</td>
289 <td>Marketing</td>
290 </tr>
291 <tr>
292 <td>104</td>
293 <td>Robert</td>
294 <td>Chen</td>
295 <td>Engineering</td>
296 </tr>
297 <tr>
298 <td>107</td>
299 <td>David</td>
300 <td>Wilson</td>
301 <td>Engineering</td>
302 </tr>
303 </tbody>
304 </table>
305 </div>
306
307 <div class="note">
308 <strong>Note:</strong> The order of operations matters! Be sure to use parentheses when combining logical operators to make your intent clear.
309 </div>
310
311 <div class="query-box">
312 <span class="comment">-- Find Engineering or Marketing employees with salaries above $80,000</span><br>
313 <span class="keyword">SELECT</span> employee_id, first_name, last_name, department, salary<br>
314 <span class="keyword">FROM</span> employees<br>
315 <span class="keyword">WHERE</span> (department <span class="operator">=</span> <span class="string">'Engineering'</span> <span class="operator">OR</span> department <span class="operator">=</span> <span class="string">'Marketing'</span>)<br>
316 <span class="operator">AND</span> salary <span class="operator">></span> <span class="number">80000</span>;
317 </div>
318 </div>
319
320 <h2>3. Using IN and BETWEEN</h2>
321 <div class="example">
322 <p>The IN operator simplifies multiple OR conditions:</p>
323
324 <div class="query-box">
325 <span class="comment">-- Find employees in specific departments</span><br>
326 <span class="keyword">SELECT</span> employee_id, first_name, last_name, department<br>
327 <span class="keyword">FROM</span> employees<br>
328 <span class="keyword">WHERE</span> department <span class="keyword">IN</span> (<span class="string">'Engineering'</span>, <span class="string">'Marketing'</span>, <span class="string">'Sales'</span>);
329 </div>
330
331 <p>The BETWEEN operator makes range filtering simpler:</p>
332
333 <div class="query-box">
334 <span class="comment">-- Find employees with salaries between $60,000 and $90,000</span><br>
335 <span class="keyword">SELECT</span> employee_id, first_name, last_name, salary<br>
336 <span class="keyword">FROM</span> employees<br>
337 <span class="keyword">WHERE</span> salary <span class="keyword">BETWEEN</span> <span class="number">60000</span> <span class="operator">AND</span> <span class="number">90000</span>;
338 </div>
339
340 <div class="result-table">
341 <table>
342 <thead>
343 <tr>
344 <th>employee_id</th>
345 <th>first_name</th>
346 <th>last_name</th>
347 <th>salary</th>
348 </tr>
349 </thead>
350 <tbody>
351 <tr>
352 <td>101</td>
353 <td>John</td>
354 <td>Smith</td>
355 <td>85000</td>
356 </tr>
357 <tr>
358 <td>103</td>
359 <td>James</td>
360 <td>Wilson</td>
361 <td>67500</td>
362 </tr>
363 <tr>
364 <td>104</td>
365 <td>Robert</td>
366 <td>Chen</td>
367 <td>67000</td>
368 </tr>
369 <tr>
370 <td>105</td>
371 <td>Michael</td>
372 <td>Rodriguez</td>
373 <td>82000</td>
374 </tr>
375 </tbody>
376 </table>
377 </div>
378 </div>
379
380 <h2>4. Pattern Matching with LIKE</h2>
381 <div class="example">
382 <p>Use the LIKE operator with wildcards for flexible text searching:</p>
383
384 <div class="query-box">
385 <span class="comment">-- Find employees whose last name starts with 'S'</span><br>
386 <span class="keyword">SELECT</span> employee_id, first_name, last_name<br>
387 <span class="keyword">FROM</span> employees<br>
388 <span class="keyword">WHERE</span> last_name <span class="keyword">LIKE</span> <span class="string">'S%'</span>;
389 </div>
390
391 <div class="result-table">
392 <table>
393 <thead>
394 <tr>
395 <th>employee_id</th>
396 <th>first_name</th>
397 <th>last_name</th>
398 </tr>
399 </thead>
400 <tbody>
401 <tr>
402 <td>101</td>
403 <td>John</td>
404 <td>Smith</td>
405 </tr>
406 </tbody>
407 </table>
408 </div>
409
410 <div class="query-box">
411 <span class="comment">-- Find employees with 'son' at the end of their last name</span><br>
412 <span class="keyword">SELECT</span> employee_id, first_name, last_name<br>
413 <span class="keyword">FROM</span> employees<br>
414 <span class="keyword">WHERE</span> last_name <span class="keyword">LIKE</span> <span class="string">'%son'</span>;
415 </div>
416
417 <div class="result-table">
418 <table>
419 <thead>
420 <tr>
421 <th>employee_id</th>
422 <th>first_name</th>
423 <th>last_name</th>
424 </tr>
425 </thead>
426 <tbody>
427 <tr>
428 <td>107</td>
429 <td>David</td>
430 <td>Wilson</td>
431 </tr>
432 <tr>
433 <td>108</td>
434 <td>Sarah</td>
435 <td>Johnson</td>
436 </tr>
437 </tbody>
438 </table>
439 </div>
440
441 <div class="query-box">
442 <span class="comment">-- Find employees with 'a' as the second letter in their first name</span><br>
443 <span class="keyword">SELECT</span> employee_id, first_name, last_name<br>
444 <span class="keyword">FROM</span> employees<br>
445 <span class="keyword">WHERE</span> first_name <span class="keyword">LIKE</span> <span class="string">'_a%'</span>;
446 </div>
447
448 <div class="result-table">
449 <table>
450 <thead>
451 <tr>
452 <th>employee_id</th>
453 <th>first_name</th>
454 <th>last_name</th>
455 </tr>
456 </thead>
457 <tbody>
458 <tr>
459 <td>102</td>
460 <td>Maria</td>
461 <td>Garcia</td>
462 </tr>
463 <tr>
464 <td>103</td>
465 <td>James</td>
466 <td>Wilson</td>
467 </tr>
468 </tbody>
469 </table>
470 </div>
471 </div>
472
473 <h2>5. Handling NULL Values</h2>
474 <div class="example">
475 <p>Use IS NULL or IS NOT NULL to filter for missing data:</p>
476
477 <div class="query-box">
478 <span class="comment">-- Find employees with no manager (they are the top-level management)</span><br>
479 <span class="keyword">SELECT</span> employee_id, first_name, last_name<br>
480 <span class="keyword">FROM</span> employees<br>
481 <span class="keyword">WHERE</span> manager_id <span class="keyword">IS NULL</span>;
482 </div>
483
484 <div class="result-table">
485 <table>
486 <thead>
487 <tr>
488 <th>employee_id</th>
489 <th>first_name</th>
490 <th>last_name</th>
491 </tr>
492 </thead>
493 <tbody>
494 <tr>
495 <td>101</td>
496 <td>John</td>
497 <td>Smith</td>
498 </tr>
499 </tbody>
500 </table>
501 </div>
502
503 <div class="warning">
504 <strong>Warning:</strong> Never use <code>= NULL</code> to check for NULL values! This will always fail. Always use <code>IS NULL</code> or <code>IS NOT NULL</code>.
505 </div>
506
507 <div class="query-box">
508 <span class="comment">-- WRONG: This will never match any rows</span><br>
509 <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> manager_id = <span class="keyword">NULL</span>;<br><br>
510
511 <span class="comment">-- CORRECT: This properly checks for NULL values</span><br>
512 <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> manager_id <span class="keyword">IS NULL</span>;
513 </div>
514 </div>
515
516 <h2>Challenge Exercises</h2>
517 <div class="challenge">
518 <p><strong>Challenge 1:</strong> Write a query to find all employees in either Engineering or Marketing departments who have a salary greater than $80,000 and were hired after January 1, 2019.</p>
519
520 <div class="query-box">
521 <span class="comment">-- Solution:</span><br>
522 <span class="keyword">SELECT</span> employee_id, first_name, last_name, department, salary, hire_date<br>
523 <span class="keyword">FROM</span> employees<br>
524 <span class="keyword">WHERE</span> (department <span class="operator">=</span> <span class="string">'Engineering'</span> <span class="operator">OR</span> department <span class="operator">=</span> <span class="string">'Marketing'</span>)<br>
525 <span class="operator">AND</span> salary <span class="operator">></span> <span class="number">80000</span><br>
526 <span class="operator">AND</span> hire_date <span class="operator">></span> <span class="string">'2019-01-01'</span>;
527 </div>
528 </div>
529
530 <div class="challenge">
531 <p><strong>Challenge 2:</strong> Write a query to find employees who either:</p>
532 <ul>
533 <li>Have a last name ending with "son" OR</li>
534 <li>Work in the "Sales" department AND earn between $50,000 and $70,000</li>
535 </ul>
536
537 <div class="query-box">
538 <span class="comment">-- Solution:</span><br>
539 <span class="keyword">SELECT</span> employee_id, first_name, last_name, department, salary<br>
540 <span class="keyword">FROM</span> employees<br>
541 <span class="keyword">WHERE</span> last_name <span class="keyword">LIKE</span> <span class="string">'%son'</span><br>
542 <span class="operator">OR</span> (department <span class="operator">=</span> <span class="string">'Sales'</span> <span class="operator">AND</span> salary <span class="keyword">BETWEEN</span> <span class="number">50000</span> <span class="operator">AND</span> <span class="number">70000</span>);
543 </div>
544 </div>
545 </div>
546</body>
547</html>This practical example demonstrates progressive filtering techniques with the WHERE clause:
These examples mirror common database tasks:
As you progress in SQL, you'll combine WHERE with other clauses like ORDER BY, GROUP BY, and JOIN to create increasingly powerful queries.
Excellent work mastering the WHERE clause! You now have the skills to filter data with precision and handle a wide range of filtering scenarios.
Next, we'll learn about Sorting & Limiting Results with the ORDER BY and LIMIT clauses. These allow you to organize your results in a meaningful order and control how many rows are returned.
Preview of Lesson 4: Sorting & Limiting Results
Get ready to put your data in perfect order! 🚀
Your progress is saved in this browser, on this device only.