LOGICAL OPERATORS

            Using Logical operators we can test the some conditions. Logical operators, like comparison operators a Boolean data type with a value of “True, False, or Unknown.

• AND
• OR
• IN
• LIKE
• BETWEEN
• ALL
• ANY
• EXISTS
• SOME
• NOT

AND: The expression evaluates to TRUE if all conditions are TRUE.

Example: Find the employee details from “EMP table”, which are getting salary more than 8000 and less than 15000.

SELECT * FROM EMP

WHERE Sal>8000 AND Sal<15000

OR: The expression evaluates to TRUE if at least one condition is TRUE.

Example: Find the employee details from “EMP table”, which are getting salary less than 8000 and more than 15000.

SELECT * FROM EMP

WHERE Sal<8000 or Sal>15000

IN: Determines whether a specified value matches any value in a sub query or a list.

Example: Find the employee details from “EMP table”, which are working under the departments 10 and 20.

SELECT * FROM EMP

WHERE Dept in (10,20)

LIKE: Determines whether a specific character string matches a specified pattern.

Example: Extract the employee details from “EMP table”, whose first name is starting with “J”.

SELECT * FROM EMP

WHERE FirstName like 'J%'

BETWEEN: Specifies a range to test.

Example: Find the employee details from “EMP table”, which are getting salary between 8000 and 15000.

SELECT * FROM EMP

WHERE Sal BETWEEN 8000 AND 15000

ALL: It Returns TRUE if all of a set of comparisons are TRUE other wise returns FALSE.

Example: Extract all employee details from “EMP table”, which are getting salary more than employees who are working under 10^th department.

SELECT * FROM EMP

WHERE Sal > ALL (SELECT Sal FROM EMP WHERE Dept=10)

ANY: ANY returns TRUE when the comparison specified is TRUE for ANY pair, otherwise, returns FALSE.

Example: Extract all employee details from “EMP table”, which are getting salary more than any one of the employees who are working under 10^th department.

SELECT * FROM EMP

WHERE Sal > ANY (SELECT Sal FROM EMP WHERE Dept=10)

EXISTS: Specifies a sub query to test for the existence of rows.

Example: I have tow table EMP1 and EMP2 (Table creation script is attached below). Check the EMP2 table records are present in EMP1 or not.

SELECT * FROM EMP1 A

WHERE EXISTS

(SELECT * FROM EMP2 B WHERE A.Dept=B.Dept)

Table creation script:

EMP1:

      CREATE TABLE EMP1

      (

      ID INT,FirstName varchar (30),LastName Varchar(30),Gender Char(1),Designation Varchar(10),

      ManagerID INT,Dept INT,Sal DECIMAL(18,4),Comm DECIMAL(18,4),HireDate DATE

      )

      Go

      INSERT INTO EMP1 VALUES (101,'Yagenti','Charan','M','Developer',105,10,4000,NULL,'2001-05-21')

      INSERT INTO EMP1 VALUES (102,'Mare','Roja','F','Developer',105,10,3500,NULL,'2005-03-18')

      INSERT INTO EMP1 VALUES (103,'Jagarla','Mahi','M','Sales man',104,20,3000,500,'2011-05-23')

      INSERT INTO EMP1 VALUES (104,'Dama','Puja','F','Manager',110,20,8000,NULL,'2009-05-19')

      INSERT INTO EMP1 VALUES (105,'Kanti','Vijay','M','Manager',110,10,10000,NULL,'2001-10-30')

      INSERT INTO EMP1 VALUES (106,'Konda','Krishna','M','Sales man',104,20,2500,600,'2010-09-05')

EMP2:

      CREATE TABLE EMP2

      (

      ID INT,FirstName varchar (30),LastName Varchar(30),Gender Char(1),Designation Varchar(10),

      ManagerID INT,Dept INT,Sal DECIMAL(18,4),Comm DECIMAL(18,4),HireDate DATE

      )

      Go

      INSERT INTO EMP2 VALUES (101,'Yagenti','Charan','M','Developer',105,10,4000,NULL,'2001-05-21')

      INSERT INTO EMP2 VALUES (102,'Mare','Roja','F','Developer',105,10,3500,NULL,'2005-03-18')

      INSERT INTO EMP2 VALUES (103,'Jagarla','Mahi','M','Sales man',104,20,3000,500,'2011-05-23')

Some: Compares a scalar value with a single-column set of values. SOME and ANY are equivalent.

NOT: Negates a Boolean input.

Example: Find the employee details from “EMP table”, which are not working under the departments 10 and 20.

SELECT * FROM EMP

WHERE Dept NOT IN (10,20)

Note: When more than one logical operator is used in a statement, the AND operators are evaluated first. You can change the order of evaluation by using parentheses.