Logical Operators
Logical Operators operate on two conditions and returns a logical output as True or False.
Following are the logical operators.
Operator Description Syntax
AND Compares two expressions. Returns true if both expressions return true, else returns false. expression1 AND expression2
OR Compares two expressions. Returns true if atleast one expression returns true, else returns false. expression1 OR expression2
NOT Reverses the value of a boolean expression. NOT expression
ALL Using a comparison operator, ALL operator compares a single scalar value against a set of values from a query output. Returns true If all the comparison between the scalar value and each set of data from the query output returns true, else returns false. scalar_value {comparison operator} ALL (subquery)
ANY Using a comparison operator, ANY operator compares a single scalar value against a set of values from a query output. Returns true If atleast one of the comparison between the scalar value and each set of data from the query output returns true, else returns false. scalar_value {comparison operator} ANY (subquery)
SOME SOME and ANY are equivalent. scalar_value {comparison operator} SOME (subquery)
BETWEEN Compares values between a range. Returns true if the scalar value is greater than or equal to the begin value and less than or equal to the end value. scalar_value BETWEEN begin_value AND end_value
EXISTS Determines whether a subquery contains any rows. Returns true if sub query contains atleast one row, else returns false. EXISTS (subquery)
IN Compares a scalar value with values in a sub query or a list. Returns true if the scalar value matches any value in the subquery or a list. scalar_value IN (subquery|list)
LIKE Compares an expression with a pattern. Unlike = comparison operator, patterns in LIKE operator can include wildcard characters. Returns true if the expression matches the pattern, else returns false expression LIKE pattern
Table: Product
ProductID ProductName Price
1 HD TV 1000
2 Play Station 400
3 MacBook 2000
4 IMAC 1500
5 IPOD 200
6 XBOX 500
7 MODEM 100
8 Nintendo 200
9 Speakers 100
10 Printer 200
11 SLR Camera 1000
12 Tablet 600
Example 1 (Logical Operator - AND)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE ProductID > 5
AND Price > 500
Output :
ProductID ProductName Price
11 SLR Camera 1000
12 Tablet 600
Description :
Here both the conditions in the WHERE clause are evaluated for each row in the Product table and returns back only those rows where both the conditions returns true.
Example 2 (Logical Operator - OR)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE ProductName = 'MacBook'
OR ProductID > 10
Output :
ProductID ProductName Price
3 MacBook 2000
11 SLR Camera 1000
12 Tablet 600
Description :
Here both the conditions in the WHERE clause are evaluated for each row in the Product table and returns back those rows where either one of the condition returns true.
Example 3 (Logical Operator - NOT)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE NOT (Price < 999)
Output :
ProductID ProductName Price
1 HD TV 1000
3 MacBook 2000
4 IMAC 1500
11 SLR Camera 1000
Description :
Here the NOT operator reverses the Boolean expression and returns back all the rows from the Product table where the condition returns false.
Example 4 (Logical Operator - ALL)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE Price > ALL (SELECT Price
FROM Product
WHERE ProductID > 5)
Output :
ProductID ProductName Price
3 MacBook 2000
4 IMAC 1500
Description :
In the above query, the sub query will return back 7 records having ProductID > 5. The outer query uses the ALL operator to return back those records where the price of the product is greater than all the records returned by the sub query. Since the max price in the sub query is 1000(from ProductID 11), only those records in the outer query with price >1000 will be returned as the output. In the output, notice that only two records (ProductID 3 and 4) have price greater than all the prices returned by the sub query.
Example 5 (Logical Operator - ANY)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE Price > ANY (SELECT Price
FROM Product
WHERE ProductID > 5)
Output :
ProductID ProductName Price
1 HD TV 1000
2 Play Station 400
3 MacBook 2000
4 IMAC 1500
5 IPOD 200
6 XBOX 500
8 Nintendo 200
10 Printer 200
11 SLR Camera 1000
12 Tablet 600
Description :
In the above query, the sub query will return back 7 records having ProductID > 5. The outer query uses the ANY operator to return back those records where the price of the product is greater than at least one of the records returned by the sub query. Since the minimum price in the sub query is 100(from ProductID 7 and 9), all the records in the outer query with price >100 will be returned as the output. In the output, notice that the only products which are not part of the output are ProductID 7 and 9, because their price is not greater than 100. If we update the comparison operator in the outer query from >(less than) to >=(greater than or equal to), then all the products will be returned back by the outer query because all products have price greater than or equal to 100.
Example 6 (Logical Operator - BETWEEN)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE Price BETWEEN 500 AND 1000
Output :
ProductID ProductName Price
1 HD TV 1000
6 XBOX 500
11 SLR Camera 1000
12 Tablet 600
Description :
Here those products having price greater than or equal to 500 and less than or equal to 1000 are returned back.
Example 7 (Logical Operator - EXISTS)
Query :
SELECT ProductID, ProductName, Price
FROM Product AS P
WHERE EXISTS (SELECT ProductID, ProductName, Price
FROM Product AS P1
WHERE P1.ProductID = P.ProductID
AND P1.Price > 1000)
Output :
ProductID ProductName Price
3 MacBook 2000
4 IMAC 1500
Description :
Here since we are using only one table, the example does not make much sense. But in general, as you can see the outer query returns back only those records which exist in the inner query.
Example 8 (Logical Operator - IN)
Query :
SELECT ProductID, ProductName, Price
FROM Product
WHERE ProductName IN (SELECT ProductName
FROM Product AS P1
WHERE P1.Price > 1000)
Output :
ProductID ProductName Price
3 MacBook 2000
4 IMAC 1500
Description :
Just like previous example, IN Operator is usually used with multiple tables. In the above example, the outer query returns back those products which has product name matching the product name returned by the sub query.