In SELECT statement we can also use logical operator with WHERE clause. There are Three logical operator AND , OR and NOT. These logical operator combine the multiple condition. These operator also called conjunctive operators which provide a facility of multiple comparison.
The result of AND operator is True if The result of all given set of condition is true. for example if we have a statement with two condition, so if First condition satisfied and second condition is also satisfied means bother condition are true then over all condition is True.
If we have table Client-Info with the following records:
client_ID | city | order_amount |
A001 | Karachi | 50000 |
A002 | Lahore | 20000 |
B001 | Karachi | 30000 |
B002 | Islamabad | 90000 |
If we want to display those record whose City is Karachi and Order-Amount is Grater then 40000 then we use the following statement.
SELECT * FROM client_info WHERE city='Karachi' AND order_amount>40000 ;
where
client_info is table name and city and order_amount are column name.
The above statement display the following records:
client_ID | city | order_amount |
A001 | Karachi | 50000 |
As you can see that there are two result with city name Karachi but one one record with ID A001 have Order amount grater the 40000 but other record B001 although have city Karachi but order amount not satisfy the second condition.
The result of OR operator is True if The result of any one condition is true from the set of given condition. For example if we have a statement with two condition, so if any one condition satisfied then over all result is true.
For example from above table if we want to display the Record whose city name is Karachi OR Order_Amount is Grater Then 40000 then we use following statement:
SELECT * FROM client_info WHERE city='Karachi' OR order_amount>40000 ;
The above statement display the following records:
client_ID | city | order_amount |
A001 | Karachi | 50000 |
B001 | Karachi | 30000 |
B002 | Islamabad | 90000 |
As you can see
First record A001 Satisfied both condition means that the city is Karachi and order amount is grater then 40000. So its display.
that there are two result whose city is Karachi and One record whose Order-Amount is grater then 40000.
2nd record A002 not Satisfied the both conditions. Means nether city is Karachi and order-amount is less then 40000. so its not selected.
3rd record B001 although not Satisfied the second condition which is order amount is grater the 40000 but first condition is satisfied which is city is Karachi. So over all result is true according to the rule of OR operator (Any one condition is true then over all condition is true). So This record is also selected.
The last record B002 also satisfied the Second condition order amount is greater the 40000 but not satisfied first condition city is equal to Karachi. So over all result is true and this record also selected.
NOT operator reverse the value of any given Boolean operator. for example if specify the name='xyz' (name is equal to xyz) then if place NOT operator before this condition just like NOT (name='xyz') now its means that name Not equal to xyz.
For example
from above table when we write the following statement:
SELECT * FROM client_info WHERE NOT(city='Karachi') ;
The above statement display the following records:
client_ID | city | order_amount |
A002 | Lahore | 20000 |
B002 | Islamabad | 90000 |
Display those record where city is not equal to Karachi.
Select Command
Distinct clause with Select Command
Where clause In SQL
Logical Operator in SQL SELECT Statement
IN Operator in SQL
BETWEEN Operator in SQL
Use of Wildcards in SQL
LIKE Operator in SQL
ORDER BY Clause in SQL
GROUP BY Clause in SQL
HAVING Clause in SQL
ALIAS In SQL
AS Keyword In SQL
INSERT INTO Command In SQL
Inserting Record In to a Table Form an other Table In SQL
UPDATE Command in SQL
DELETE Command in SQL
AVG Function in SQL