The AND and OR operators in SQL can be used with the WHERE clause to make more precise conditions for fetching data from a database through the combination of more than one condition together.
The AND operator is typically used to set multiple conditions with the WHERE clause, with UPDATE, SELECT, or DELETE SQL queries.
Below is an example of the AND operator;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 5000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 12000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 9000 |
SELECT * FROM Emp WHERE salary < 10000 AND age > 25
The query used above will return records where the salary is less than 10000 and the age is greater than 25.
Also, we have used the AND operator to specify two conditions alongside the where clause.
eid | name | age | salary |
---|---|---|---|
402 | Shane | 29 | 8000 |
405 | Tiger | 35 | 9000 |
The OR operator is basically used by combining multiple conditions with the WHERE clause. However, the difference between AND and OR is their behavior.
Also, you can use AND combine two or more conditions, and the records satisfying all the specified conditions will be there in the result. While, with the OR operator, at least one condition from the conditions specified must be satisfied by any record to be in the resultset.
Below is an example of an OR operator;
Let’s consider the Emp table:
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 5000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 12000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 9000 |
SELECT * FROM Emp WHERE salary > 10000 OR age > 25
The query above is used to return records, where either salary is greater than 10000 or age is greater than 25.
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 12000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 9000 |