WHERE

24min
In this section, you will learn how to use the WHERE clauses to filter rows returned by a SELECT query with a specified condition.
OverviewThe WHERE clause returns a specific value from a table or multiple tables based on specified conditions. It will filter out records you do not want to be included and only returns the exact result when the condition is fulfilled.
SyntaxThe basic syntax of the WHERE clause is as follows −
Syntax
|
SELECT column1, column2, ...
FROM table_name
WHERE [condition]
SELECT column1, column2, ...
FROM table_name
WHERE [condition]
﻿
Let’s explore the above syntax:
SELECT column1, column2, ... defines the columns where the records will be displayed.

FROM table_name sets the table name where the records will be taken from.

WHERE [condition]specifies the search condition using comparison or logical operators (e.g., >, =, LIKE)
💡 It starts with the FROM clause -> then it executes the WHERE condition -> after that, it will SELECT the specified columns.
ExamplesLet’s assume that we have a table salary with records as follows:
Create a table
|
CREATE TABLE salary (
  empid int,
  empname string,
  empdept string,
  empaddress string,
  empsalary int
);
INSERT INTO salary 
    (empid, empname, empdept, empaddress, empsalary) 
VALUES 
    (2001,'Paul','HR', 'California', null ),
    (2002,'Brandon','Product', 'Norway', 15000),
    (2003,'Bradley','Marketing', 'Texas', null),
    (2004,'Lisa','Marketing', 'Houston', 10000),
    (2005,'Emily','Marketing', 'Texas', 20000),
    (2006,'Bobby','Finance', 'Seattle', 20000),
    (2007,'Parker','Project', 'Texas', 45000);
CREATE TABLE salary (
  empid int,
  empname string,
  empdept string,
  empaddress string,
  empsalary int
);
INSERT INTO salary 
    (empid, empname, empdept, empaddress, empsalary) 
VALUES 
    (2001,'Paul','HR', 'California', null ),
    (2002,'Brandon','Product', 'Norway', 15000),
    (2003,'Bradley','Marketing', 'Texas', null),
    (2004,'Lisa','Marketing', 'Houston', 10000),
    (2005,'Emily','Marketing', 'Texas', 20000),
    (2006,'Bobby','Finance', 'Seattle', 20000),
    (2007,'Parker','Project', 'Texas', 45000);
﻿
Display the table
|
SELECT * FROM salary;
SELECT * FROM salary;
﻿
It will create a table as shown below:
The salary table
|
+--------+-----------+------------+-------------+------------+
| empid  | empname   |  empdept   | empaddress  | empsalary  |
+--------+-----------+------------+-------------+------------+
| 2001   | Paul      | HR         | California  | null       |  
| 2002   | Brandon   | Product    | Norway      | 15000      |
| 2003   | Bradley   | Marketing  | Texas       | null       |
| 2004   | Lisa      | Marketing  | Houston     | 10000      |
| 2005   | Emily     | Marketing  | Texas       | 20000      |
| 2006   | Bobby     | Finance    | Seattle     | 20000      |
| 2007   | Parker    | Project    | Texas       | 45000      |
+--------+-----------+------------+-------------+------------+
+--------+-----------+------------+-------------+------------+
| empid  | empname   |  empdept   | empaddress  | empsalary  |
+--------+-----------+------------+-------------+------------+
| 2001   | Paul      | HR         | California  | null       |  
| 2002   | Brandon   | Product    | Norway      | 15000      |
| 2003   | Bradley   | Marketing  | Texas       | null       |
| 2004   | Lisa      | Marketing  | Houston     | 10000      |
| 2005   | Emily     | Marketing  | Texas       | 20000      |
| 2006   | Bobby     | Finance    | Seattle     | 20000      |
| 2007   | Parker    | Project    | Texas       | 45000      |
+--------+-----------+------------+-------------+------------+
﻿
#Case 1: WHERE clause with = OperatorHere we will be using the “equal” operator to look up the employee who works in the Marketing department:
Where query
|
SELECT empname, empdept
FROM salary
WHERE empdept = 'Marketing';
SELECT empname, empdept
FROM salary
WHERE empdept = 'Marketing';
﻿
The above command will create the following result:
Output
|
+------------+-------------+
| empname    | empdept     |
+------------+-------------+
| Bradley    | Marketing   |
| Emily      | Marketing   | 
| Lisa       | Marketing   |
+------------+-------------+
+------------+-------------+
| empname    | empdept     |
+------------+-------------+
| Bradley    | Marketing   |
| Emily      | Marketing   | 
| Lisa       | Marketing   |
+------------+-------------+
﻿
⚠️ Oops, watch out! The value defined in the WHERE clause’s condition is case-sensitive, so ensure that you specify the correct and precise value.
#Case 2: WHERE clause with != OperatorHere we will be using the “not equal” operator to look up the employee who doesn’t live in Texas:
Where query
|
SELECT empname, empdept, empaddress
FROM salary
WHERE empaddress != 'Texas';
SELECT empname, empdept, empaddress
FROM salary
WHERE empaddress != 'Texas';
﻿
ℹ️ We can use the <> operator for another “not equal” operator.
The above query will give the following result:
Output
|
+------------+------------+--------------+
| empname    | empdept    | empaddress   |
+------------+------------+--------------+
| Paul       | HR         | California   | 
| Brandon    | Product    | Norway       | 
| Lisa       | Marketing  | Houston      |
| Bobby      | Finance    | Seattle      |
+------------+------------+--------------+
+------------+------------+--------------+
| empname    | empdept    | empaddress   |
+------------+------------+--------------+
| Paul       | HR         | California   | 
| Brandon    | Product    | Norway       | 
| Lisa       | Marketing  | Houston      |
| Bobby      | Finance    | Seattle      |
+------------+------------+--------------+
﻿
⚠️ The value defined in the WHERE clause’s condition is case-sensitive. If you set ‘texas,’ it will return all records from the salary table. 
#Case 3: WHERE clause with > OperatorHere we will be using the “greater than” operator to figure out who has a salary above 20000:
Where query
|
SELECT empname, empdept, empsalary
FROM salary
WHERE empsalary > 20000;
SELECT empname, empdept, empsalary
FROM salary
WHERE empsalary > 20000;
﻿
ℹ️ We can use the < operator for a “less than” condition.
The output will let us know that Parker has a salary greater than 20000:
Output
|
+------------+------------+-------------+
| empname    | empdept    | empsalary   |
+------------+------------+-------------+
| Parker     | Project    | 45000       | 
+------------+------------+-------------+
+------------+------------+-------------+
| empname    | empdept    | empsalary   |
+------------+------------+-------------+
| Parker     | Project    | 45000       | 
+------------+------------+-------------+
﻿
#Case 4: WHERE clause with <= OperatorHere we will be using the “less than or equal to” operator to see who has a salary less than or equal to 15000:
Where query
|
SELECT empname, empdept, empsalary
FROM salary
WHERE empsalary <= '15000';
SELECT empname, empdept, empsalary
FROM salary
WHERE empsalary <= '15000';
﻿
ℹ️ We can use the >= operator for a “greater than or equal to” condition.
The output will let us know that Brandon has a salary equal to 15000 and Lisa has a salary of less than 15000:
Output
|
+------------+------------+-------------+
| empname    | empdept    | empsalary   |
+------------+------------+-------------+
| Brandon    | Product    | 15000       | 
| Lisa       | Marketing  | 10000       |
+------------+------------+-------------+
+------------+------------+-------------+
| empname    | empdept    | empsalary   |
+------------+------------+-------------+
| Brandon    | Product    | 15000       | 
| Lisa       | Marketing  | 10000       |
+------------+------------+-------------+
﻿
#Case 5: WHERE clause with LIKE OperatorHere we will use the “like” operator to retrieve the employee whose first name starts with Br.
Where query
|
SELECT * FROM salary
WHERE empname LIKE 'Br%';
SELECT * FROM salary
WHERE empname LIKE 'Br%';
﻿
ℹ️ Do the reverse to get the result based on the last string, %string.
We will get an output where the above query fetches Brandon & Bradley.
Output
|
+---------+------------+--------------+--------------+-----------+
| empid   | empname    | empdept     | empaddress   | empsalary  |
+---------+------------+-------------+--------------+------------+
| 2002    | Brandon    | Product     | Norway       | null       |
| 2003    | Bradley    | Marketing   | Texas        | 45000      |
+---------+------------+-------------+--------------+------------+
+---------+------------+--------------+--------------+-----------+
| empid   | empname    | empdept     | empaddress   | empsalary  |
+---------+------------+-------------+--------------+------------+
| 2002    | Brandon    | Product     | Norway       | null       |
| 2003    | Bradley    | Marketing   | Texas        | 45000      |
+---------+------------+-------------+--------------+------------+
﻿
#Case 6: WHERE clause with IS NULL OperatorHere we will use the “is null” operator to search for the employee who doesn’t have a salary value. It will return true and display the result set if a value is NULL; otherwise, it will return false with no result set.
Where query
|
SELECT * FROM salary
WHERE empsalary IS NULL;
SELECT * FROM salary
WHERE empsalary IS NULL;
﻿
The above command will create the following result:
Output
|
+---------+------------+-------------+--------------+------------+
| empid   | empname    | empdept     | empaddress   | empsalary  |
+---------+------------+-------------+--------------+------------+
| 2001    | Paul       | HR          | California   | null       |
| 2003    | Brandon    | Product     | Norway       | null       |
+---------+------------+-------------+--------------+------------+
+---------+------------+-------------+--------------+------------+
| empid   | empname    | empdept     | empaddress   | empsalary  |
+---------+------------+-------------+--------------+------------+
| 2001    | Paul       | HR          | California   | null       |
| 2003    | Brandon    | Product     | Norway       | null       |
+---------+------------+-------------+--------------+------------+
﻿
﻿