The SELECT statement in SQL returns either all the columns (if we use *) or specific columns (if we provide the names of specific columns in the query), and we have seen this in practice in the previous article SQL For Beginners - SELECT statement.
But, there are many situations where we want to retrieve records based on certain conditions. For instance, we need the StudentID of all the Students whose score is below 35%, or we need EmployeeID of all the Employees whose salary is more than 25,000. This can be achieved using the WHERE Clause.
The WHERE clause allows us to retrieve data as per our need. The WHERE clause is followed by Condition / Expression. Based on the Condition / Expression, we get the filtered data.
Syntax:
SELECT columnList
FROM Table_Name
WHERE Condition/Expression;
Example:
Consider the following Student table:
![]()
Now, we need the data of the Student whose City is Delhi. This can be done with the help of the following query:
- Select * from Students where City='Delhi';
![]()
As you can see the snippet of output, the details of the Student having
City as
Delhi is displayed.
If we need only the StudentID of the Student belonging to Delhi, then the query would be:
- Select StudentID from Students where City='Delhi';
![]()
This was a simple usage of the SQL WHERE Clause. Now, let us see the usage of
AND and
OR operators.
AND Operator:
AND operator will retrieve the data where both the conditions will be True.
Syntax:
SELECT columnList
FROM Table_Name
WHERE condition1 AND condition2;
Example:
- Select * from Students where City='Mumbai' AND marks<70;
![]()
As you can see in the output, the record of Peter is retrieved because his city is
Mumbai and his marks are less than 70.
Tom and Raj also have marks less than 70. But, why is only Peter's seen in the output? Because, we have used AND operator, it will make sure that we get the result which satisfies both the condition.
OR Operator:
OR Operator will retrieve the records even if a single condition is evaluated to be true.
Syntax:
SELECT columnList
FROM Table_Name
WHERE condition1 OR condition2;
Example
- Select * from Students where City='Pune' OR marks<70;
![]()
Here, the records of
Students having
City as
Delhi and
Mumbai are also retrieved. This is because they have marks less than 70.
As we have seen
< and = operators in the examples above, we can also
use > , <> , >= , <= , etc operators in the expressions. In the next article, we will learn about the various types of operators and their usage.
Stay tuned! Keep learning!