{"id":12303,"date":"2021-01-10T16:12:34","date_gmt":"2021-01-11T00:12:34","guid":{"rendered":"https:\/\/careerkarma.com\/blog\/?p=12303"},"modified":"2023-12-01T04:07:56","modified_gmt":"2023-12-01T12:07:56","slug":"sql-where","status":"publish","type":"post","link":"https:\/\/careerkarma.com\/blog\/sql-where\/","title":{"rendered":"SQL WHERE Statement: A How-To Guide"},"content":{"rendered":"\n

The SQL WHERE clause limits how many records are returned or affected by a command. It is used with the SELECT, UPDATE, and DELETE clauses. You can use the AND keyword to specify multiple conditions which you want records affected by your command to meet.<\/em><\/p>\n\n\n\n

You may want to filter out the results of a query using specific conditions. For example, you may want to get a list of employees who are sales managers. Or you may want to find out who started working for a company after a particular date.<\/p>\n\n\n\n

You can use the WHERE clause to filter out specific records while running an SQL command. This statement can work in conjunction with SELECT, UPDATE, and DELETE clauses. In this guide, we\u2019ll cover how to use the WHERE clause to filter records in SQL.<\/p>\n\n\n\n

SQL WHERE Clause<\/h2>\n\n\n\n

The SQL WHERE clause filters records that meet a particular condition, or a set of conditions. The WHERE clause comes after the \u201cFROM\u201d clause in an SQL statement.<\/p>\n\n\n\n

Here is the syntax for this statement:<\/p>\n\n\n\n

SELECT * FROM table WHERE column comparison value;<\/pre><\/div>\n\n\n\n
“SELECT * FROM table” is a standard select query. We use the WHERE statement to limit the records our query returns. “column” refers to the column in which a condition needs to be met. “comparison” and “value” represent the comparison we want to make.<\/p>\n\n\n\n
Potential comparisons include:<\/p>\n\n\n\n
WHERE name == "Jeff";\nWHERE age > 15;\nWHERE salary < 50000;<\/pre><\/div>\n\n\n\n
You can specify multiple WHERE statements using the SQL AND keyword<\/a>:<\/p>\n\n\n\n
SELECT * FROM table WHERE column1 comparison1 value1 AND column2 comparison2 value2;<\/p>\n\n\n\n
Let’s walk through an example of a WHERE statement.<\/p>\n\n\n\n
SQL WHERE Example: SELECT<\/h2>\n\n\n\n
We want to get a list of employees who are senior sales associates. To do so, we could use the following SQL SELECT statement<\/a>:<\/p>\n\n\n\n
SELECT name, title FROM employees WHERE title = 'Senior Sales Associate';<\/pre><\/div>\n\n\n\n
The query returns the following rows that meet our criteria:
<\/p>\n\n\n\n
 name  |         title          <\/p>\n\n\n\n
——-+————————<\/p>\n\n\n\n
 Geoff | Senior Sales Associate<\/p>\n\n\n\n
 Adam  | Senior Sales Associate<\/p>\n\n\n\n
(2 rows)<\/p>\n\n\n\n
All the employees above have the title \u201cSenior Sales Associate.\u201d\n\n<\/p>\n\n\n\n
Let\u2019s use another example. Say we wanted to get a list of every employee who earns over $35,000 per year and whose name is not Adam. We could use the following SQL statement:<\/p>\n\n\n\n
SELECT name, salary FROM employees WHERE salary > 35000 AND name <> "Adam";<\/pre><\/div>\n\n\n\n
Our query returns the following list:
<\/p>\n\n\n\n
 name  | salary <\/p>\n\n\n\n
——-+——–<\/p>\n\n\n\n
 Geoff |  38000<\/p>\n\n\n\n
 Emma  |  50000<\/p>\n\n\n\n
 Jonah |  50000<\/p>\n\n\n\n
 Adam  |  38000<\/p>\n\n\n\n
(4 rows)<\/p>\n\n\n\n
Adam’s name is not in our results set even though he earns over $35,000 per year. This is because we excluded his name with the <> operator in our query.<\/p>\n\n\n\n
WHERE SQL Clause: Update and Delete<\/h2>\n\n\n\n
The SQL WHERE clause works with UPDATE and DELETE statements.<\/p>\n\n\n\n
For example, say you want to update the branch names individual employees work for if there is a company restructuring. You could do this using the UPDATE statement.<\/p>\n\n\n\n
We want to change Luke\u2019s branch from \u201cBoston\u201d to \u201cCambridge,\u201d where his new office resides. We can do this using an SQL UPDATE statement<\/a>:<\/p>\n\n\n\n
UPDATE employees SET branch = 'Cambridge' WHERE name = 'Luke';<\/pre><\/div>\n\n\n\n
If we query to find out Luke\u2019s branch, we get the following result set:<\/p>\n\n\n\n
 name |  branch   <\/p>\n\n\n\n
——+———–<\/p>\n\n\n\n
 Luke | Cambridge<\/p>\n\n\n\n
(1 row)<\/p>\n\n\n\n
We can use the WHERE clause with the <\/a>SQL DELETE command<\/a>.<\/p>\n\n\n\n
Say we wanted to delete the records of every employee who works at the Albany branch, which has been closed. We could do this using the following command:\n\n<\/p>\n\n\n\n
When we get our list of employees, the number of rows returned is six instead of eight:<\/p>\n\n\n\n
DELETE FROM employees WHERE branch = 'Albany';<\/pre><\/div>\n\n\n\n
Our code returns six rows:<\/p>\n\n\n\n
  name  |    branch     <\/p>\n\n\n\n
——–+—————<\/p>\n\n\n\n
 Mike   | Stamford<\/p>\n\n\n\n
 Hannah | San Francisco<\/p>\n\n\n\n
 Geoff  | San Francisco<\/p>\n\n\n\n
 Alexis | Boston<\/p>\n\n\n\n
 Adam   | Sacramento<\/p>\n\n\n\n
 Luke   | Cambridge<\/p>\n\n\n\n
(6 rows)<\/p>\n\n\n\n
SQL Operators<\/h2>\n\n\n\n
The SQL WHERE clause uses logical operators to filter out records. In our first example, we used the \u201cis equal to\u201d operator to check for the names of senior sales associates.\n\n<\/p>\n\n\n\n
There are a number of logical operators we can use to filter out records when utilizing the clause. These are as follows:\n<\/p>\n\n\n\n
Operator<\/td>Description<\/td>Example<\/td><\/tr>
=<\/td>Is equal to<\/td>WHERE name = \u2018Jake\u2019<\/td><\/tr>
><\/td>Is greater than<\/td>age > 20<\/td><\/tr>
<<\/td>Is less than<\/td>age < 20<\/td><\/tr>
>=<\/td>Greater than or equal to<\/td>salary => 50000<\/td><\/tr>
<=<\/td>Less than or equal to<\/td>salary <= 40000<\/td><\/tr>
LIKE<\/a><\/td>Pattern matching<\/td>name LIKE \u2018Jo*\u2019<\/td><\/tr>
IN<\/td>Check if a specified value matches any within a list<\/td>title IN (\u2018Sales Associate\u2019, \u2018Director of Sales\u2019)<\/td><\/tr>
BETWEEN<\/a><\/td>Check if the specified value is within a range of other values<\/td>employee_month_awards BETWEEN 1 AND 5<\/td><\/tr><\/tbody><\/table>\n\n\n\n