The WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. For example, when you want to see the information about students in class 10th only then you do need the information about the students in other class. Retrieving information about all the students would increase the processing time for the query.
So SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved. The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.
Syntax of SQL WHERE Clause:
WHERE {column or expression} comparison-operator value
Syntax for a WHERE clause with Select statement is:
SELECT column_list FROM table-name
WHERE condition;
- column or expression – Is the column of a table or a expression
- comparison-operator – operators like = < > etc.
- value – Any user value or a column name for comparison
Example:
SQL WHERE Statement
SELECT * FROM Individual WHERE FirstName = 'Homer'
Source Table
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 1 | Fred | Flinstone | freddo |
| 2 | Homer | Simpson | homey |
| 3 | Homer | Brown | notsofamous |
| 4 | Ozzy | Ozzbourne | sabbath |
| 5 | Homer | Gain | noplacelike |
Result
Given there are 3 people with the first name of “Homer”, the results will look like this:
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 2 | Homer | Simpson | homey |
| 3 | Homer | Brown | notsofamous |
| 5 | Homer | Gain | noplacelike |
Multiple Conditions
You can filter records based on more than one condition using operators. Two common operators are the AND and OR operators.
AND Operator
The AND operator filters the query to only those records that satisfy both the first condition and the second condition.
SELECT * FROM Individual WHERE FirstName = 'Homer' AND LastName = 'Brown'
Result
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 3 | Homer | Brown | notsofamous |
OR Operator
The OR operator filters the query to only those records that satisfy either one or the other condition.
SELECT * FROM Individual WHERE FirstName = 'Homer' OR LastName = 'Ozzbourne'
Result
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 2 | Homer | Simpson | homey |
| 3 | Homer | Brown | notsofamous |
| 5 | Homer | Gain | noplacelike |
| 4 | Ozzy | Ozzbourne | sabbath |
