SQL - WHERE Clause
The SELECT query can also have an optional WHERE clause to filter the data. The WHERE clause can include one or more boolean conditions to filter out data of the tables.
The WHERE clause always comes after the FROM clause and before GROUP BY, HAVING, and ORDER BY clauses.
Syntax:
SELECT column1, column2,...columnN FROM table_name WHERE conditions
For the demo purpose, we will use the following Employee
and Department
tables in all examples.
EmpId | FirstName | LastName | PhoneNo | Salary | DeptId | |
---|---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | '650.127.1834' | 33000 | 1 |
2 | 'James' | 'Bond' | 2 | |||
3 | 'Neena' | 'Kochhar' | '[email protected]' | '123.456.4568' | 17000 | 2 |
4 | 'Lex' | 'De Haan' | '[email protected]' | '123.456.4569' | 15000 | 1 |
DeptId | Name |
---|---|
1 | 'Finance' |
2 | 'HR' |
The WHERE clause can contain one or more conditions that can use conditional operators to filter out the result data. Consider the following query.
SELECT * FROM Employee
WHERE Salary > 16000;
In the above query, the condition Salary > 16000
returns rows where the value in the Salary
column is greater than 16000. The above query will return the following result.
EmpId | FirstName | LastName | PhoneNo | Salary | DeptId | |
---|---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | '650.127.1834' | 33000 | 1 |
3 | 'Neena' | 'Kochhar' | '[email protected]' | '123.456.4568' | 17000 | 2 |
The following query uses the BETWEEN operator in the WHERE clause.
SELECT * FROM Employee
WHERE Salary BETWEEN 15000 AND 20000;
In the above query, the condition Salary BETWEEN 15000 AND 20000
returns rows where the value in the Salary
column is between 15000 and 20000 (including both values). The above query will get the following records.
EmpId | FirstName | LastName | PhoneNo | Salary | DeptId | |
---|---|---|---|---|---|---|
3 | 'Neena' | 'Kochhar' | '[email protected]' | '123.456.4568' | 17000 | 2 |
4 | 'Lex' | 'De Haan' | '[email protected]' | '123.456.4569' | 15000 | 1 |
Multiple Conditions in WHERE Clause
The WHERE clause can contain multiple conditions using AND and OR operators. The following query uses logical operator AND to specify two conditions to filter out data.
SELECT * FROM Employee
WHERE DeptId = 1 AND Salary > 20000;
In the above query, the WHERE condition DeptId = 1 AND Salary > 20000
specifies two conditions separated by AND operator. This will return rows from the Employee
table where the value of DeptId
is 1 and Salary
is greater than 20000.
EmpId | FirstName | LastName | PhoneNo | Salary | DeptId | |
---|---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | '650.127.1834' | 33000 | 1 |
Subquery in WHERE Clause
The WHERE condition can also use the resulted value of a subquery, as shown below.
SELECT * FROM Employee
WHERE DeptId = (SELECT DeptId FROM Department WHERE Name = "HR");
In the above query, the WHERE condition is WHERE DeptId = (SELECT DeptId FROM Department WHERE Name = "HR")
. So, first, the subquery SELECT DeptId FROM Department WHERE Name = "HR"
would be executed, and the resulted DeptId
value will be used to filter out the rows.
EmpId | FirstName | LastName | PhoneNo | Salary | DeptId | |
---|---|---|---|---|---|---|
2 | 'James' | 'Bond' | 2 | |||
3 | 'Neena' | 'Kochhar' | '[email protected]' | '123.456.4568' | 17000 | 2 |
Conditional Operators
The following operators can be used in the WHERE conditions.
Operator | Description |
---|---|
= | Equal |
> | Greater than |
< | Less than |
>= | Greater than or equal |
<= | Less than or equal |
<> or != | Not equal. In some databases, the != is used to compare values which are not equal. |
BETWEEN | Between some range |
LIKE | Search for a pattern |
IN | To specify multiple possible values for a column |