Operator in SQL SELECT Statement

Logical Operator in SQL SELECT Statement

 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.

AND operator

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.

OR Operator

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

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.