WHERE Clause in SQL
In the previous tutorial, we have discussed the GROUP BY in SQL and filtering of the data by sorting them. Similar to that we shall discuss, WHERE clause in SQL in combination with the other operators. So, we shall get to know the few operators in this tutorial, which are very useful to filter the data and obtain the output accordingly.
Using the AND Operator
AND operator will append the conditions with the WHERE clause, to filter the data by more than one column. AND is a keyword, which is used with the WHERE clauses such that rows matching the given conditions are being retrieved.
Example
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = ‘DLL01’ AND prod_price <= 4;
Output
prod_id prod_price prod_name
——- ———- ——————–
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
The above example can be clearly evaluated as that it will retrieve the specific attributes from the table with the mentioned vendor id and the product price less than 4.
Using the OR Operator
The OR operators act completely opposite to that of the AND operator. Unlike AND operator the OR operator will retrieve the rows that match either condition. In many cases, the DBMS do not even evaluate the second condition of the WHERE OR clause if the first one is satisfied. If the first condition is met, the respective row is retrieved, without the involvement of the second condition.
Example
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = ‘DLL01’ OR vend_id = ‘BRS01’;
Output
prod_name prod_price
——————- ———-
Fish bean bag toy 3.4900
Bird bean bag toy 3.4900
Rabbit bean bag toy 3.4900
8 inch teddy bear 5.9900
12 inch teddy bear 8.9900
18 inch teddy bear 11.9900
Raggedy Ann 4.9900
The above SQL command will retrieve the product name and price form the respective table, by either of the two vendors, whose id is specified. The OR operator will insist the DBMS to match either of the vendor id but not the both. Here, in this case, the AND operator will not return any rows, because there might not be any rows in common for both the vendor id.
Using the IN Operator
The IN operator contains a range of conditions out of which one can be matched. The IN operator syntax contain the parentheses and the range of conditions with each separated by the comma.
Example
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN (‘DLL01′,’BRS01’)
ORDER BY prod_name;
Output
prod_name prod_price
——————- ———-
12 inch teddy bear 8.9900
18 inch teddy bear 11.9900
8 inch teddy bear 5.9900
Bird bean bag toy 3.4900
Fish bean bag toy 3.4900
Rabbit bean bag toy 3.4900
Raggedy Ann 4.9900
Here the IN operator acts similar to that of the OR operator, but if there are a list of conditions to be evaluated it is better to use the IN operator compared to OR operator.
Using the NOT Operator
The NOT operator used in the WHERE clause, will negate the condition whatever comes to next of it. Unlike other operators NOT is used just before the column to perform the data filtering.
Example
SELECT prod_name
FROM Products
WHERE NOT vend_id = ‘DLL01’
ORDER BY prod_name;
Output
prod_name
——————
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
King doll
Queen doll
In this example, the products sold by all the vendors are shown, except that of the vendor with the mentioned id.
This is the end of the tutorial about the advanced data filtering operators and the examples of WHERE clause in SQL with the different operators. For any sort of doubts or the questions in this tutorial, you can reach out through the comment box.
Related Tutorials