Saturday 23 July 2016

Logical Operators

These are used to create complex queries.

Operator
Description
ALL
Compare a value to all values in another value set.
ANY
Compare a value to any applicable value in the list according to the condition.
AND
(conditio1 AND condition2) : It is true if both the conditions are true, else false.
BETWEEN
Search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS
Search for the presence of a row in a specified table that meets certain criteria.
IN
Compare a value to a list of literal values that have been specified.
LIKE
Compare a value to similar values using wildcard operators.

NOT
The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN.
OR
The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULL
Check a value with a NULL value.

UNIQUE
The UNIQUE operator searches every row of a specified table for uniqueness

Following is sample data.
CREATE TABLE employee(
  id int PRIMARY KEY,
  name VARCHAR(25),
  age int,
  salary DECIMAL(10, 4),
  mailId VARCHAR(25),
  city VARCHAR(10)
);

INSERT INTO employee VALUES (1, "Hari", 28, 12345.67, "hari@hari.com", "Bangalore");
INSERT INTO employee VALUES (2, "Sandesh", 31, 98345.00, "sandesh@sandesh.com", "Trivendram");
INSERT INTO employee VALUES (3, "Phalgum", 33, 119345.67, "phalgun@hari.com", "Hyderabad");
INSERT INTO employee VALUES (4, "Manju", 36, 87666.87, "manju@sandesh.com", "Bangalore");
INSERT INTO employee VALUES (5, "Rakesh", 26, 38000, "rakesh@hari.com", "Bangalore");
INSERT INTO employee VALUES (6, "Sankalp", 38, 87645.67, "sankalp@sankalp.com", "Chenai");
INSERT INTO employee VALUES (7, "Vadiraj", 40, 12345.67, "vadi@hari.com", "Bangalore");
INSERT INTO employee VALUES (8, "Prasob", 37, 12345.67, "prasob@sandesh.com", "Trivendram");
INSERT INTO employee (id, name, salary) VALUES (9, "Kesav", 123457.89);


mysql> CREATE TABLE employee(
    ->   id int PRIMARY KEY,
    ->   name VARCHAR(25),
    ->   age int,
    ->   salary DECIMAL(10, 4),
    ->   mailId VARCHAR(25),
    ->   city VARCHAR(10)
    -> );
ERROR 1050 (42S01): Table 'employee' already exists
mysql> drop table employee;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE employee(
    ->   id int PRIMARY KEY,
    ->   name VARCHAR(25),
    ->   age int,
    ->   salary DECIMAL(10, 4),
    ->   mailId VARCHAR(25),
    ->   city VARCHAR(10)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employee VALUES (1, "Hari", 28, 12345.67, "hari@hari.com", "Bangalore");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (2, "Sandesh", 31, 98345.00, "sandesh@sandesh.com", "Trivendram");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (3, "Phalgum", 33, 119345.67, "phalgun@hari.com", "Hyderabad");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (4, "Manju", 36, 87666.87, "manju@sandesh.com", "Bangalore");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (5, "Rakesh", 26, 38000, "rakesh@hari.com", "Bangalore");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (6, "Sankalp", 38, 87645.67, "sankalp@sankalp.com", "Chenai");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (7, "Vadiraj", 40, 12345.67, "vadi@hari.com", "Bangalore");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (8, "Prasob", 37, 12345.67, "prasob@sandesh.com", "Trivendram");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO employee (id, name, salary) VALUES (9, "Kesav", 123457.89);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  1 | Hari    |   28 |  12345.6700 | hari@hari.com       | Bangalore  |
|  2 | Sandesh |   31 |  98345.0000 | sandesh@sandesh.com | Trivendram |
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   36 |  87666.8700 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 |  38000.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 |  87645.6700 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   40 |  12345.6700 | vadi@hari.com       | Bangalore  |
|  8 | Prasob  |   37 |  12345.6700 | prasob@sandesh.com  | Trivendram |
|  9 | Kesav   | NULL | 123457.8900 | NULL                | NULL       |
+----+---------+------+-------------+---------------------+------------+
9 rows in set (0.00 sec)



AND operator
Get all employees whose salary is > 70000 and city is Bangalore.

mysql> SELECT * FROM employee WHERE salary > 70000 AND city="Bangalore";
+----+-------+------+------------+-------------------+-----------+
| id | name  | age  | salary     | mailId            | city      |
+----+-------+------+------------+-------------------+-----------+
|  4 | Manju |   36 | 87666.8700 | manju@sandesh.com | Bangalore |
+----+-------+------+------------+-------------------+-----------+
1 row in set (0.00 sec)



Get all employees whose age is > 30, and staying in either Bangaore (or) in Chennai

mysql> SELECT * FROM employee WHERE age > 30 AND (city="Bangalore" OR city = "Chennai");
+----+---------+------+------------+-------------------+-----------+
| id | name    | age  | salary     | mailId            | city      |
+----+---------+------+------------+-------------------+-----------+
|  4 | Manju   |   36 | 87666.8700 | manju@sandesh.com | Bangalore |
|  7 | Vadiraj |   40 | 12345.6700 | vadi@hari.com     | Bangalore |
+----+---------+------+------------+-------------------+-----------+



OR Operator
Get all employees who are staying in Chennai (or) salary > 60000.


mysql> SELECT * FROM employee WHERE city = "Chennai" OR salary > 60000;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  2 | Sandesh |   31 |  98345.0000 | sandesh@sandesh.com | Trivendram |
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   36 |  87666.8700 | manju@sandesh.com   | Bangalore  |
|  6 | Sankalp |   38 |  87645.6700 | sankalp@sankalp.com | Chenai     |
|  9 | Kesav   | NULL | 123457.8900 | NULL                | NULL       |
+----+---------+------+-------------+---------------------+------------+
5 rows in set (0.00 sec)
                       

IS NULL
Get all employees whose city is null.

mysql> SELECT * FROM employee WHERE city IS NULL;
+----+-------+------+-------------+--------+------+
| id | name  | age  | salary      | mailId | city |
+----+-------+------+-------------+--------+------+
|  9 | Kesav | NULL | 123457.8900 | NULL   | NULL |
+----+-------+------+-------------+--------+------+
1 row in set (0.00 sec) 
  

Get all employees whose city is not null.

mysql> SELECT * FROM employee WHERE city IS NOT NULL;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  1 | Hari    |   28 |  12345.6700 | hari@hari.com       | Bangalore  |
|  2 | Sandesh |   31 |  98345.0000 | sandesh@sandesh.com | Trivendram |
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   36 |  87666.8700 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 |  38000.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 |  87645.6700 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   40 |  12345.6700 | vadi@hari.com       | Bangalore  |
|  8 | Prasob  |   37 |  12345.6700 | prasob@sandesh.com  | Trivendram |
+----+---------+------+-------------+---------------------+------------+
8 rows in set (0.00 sec)



LIKE
Get all employees whose mailId ends with hari.com.

mysql> SELECT * FROM employee WHERE mailId like "%hari.com";
+----+---------+------+-------------+------------------+-----------+
| id | name    | age  | salary      | mailId           | city      |
+----+---------+------+-------------+------------------+-----------+
|  1 | Hari    |   28 |  12345.6700 | hari@hari.com    | Bangalore |
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com | Hyderabad |
|  5 | Rakesh  |   26 |  38000.0000 | rakesh@hari.com  | Bangalore |
|  7 | Vadiraj |   40 |  12345.6700 | vadi@hari.com    | Bangalore |
+----+---------+------+-------------+------------------+-----------+
4 rows in set (0.00 sec) 



ALL
Get all employees whose age is greater than all the employees who are staying in Trivendram.

mysql> SELECT * FROM employee WHERE age > ALL(SELECT age FROM employee where city="Trivendram");
+----+---------+------+------------+---------------------+-----------+
| id | name    | age  | salary     | mailId              | city      |
+----+---------+------+------------+---------------------+-----------+
|  6 | Sankalp |   38 | 87645.6700 | sankalp@sankalp.com | Chenai    |
|  7 | Vadiraj |   40 | 12345.6700 | vadi@hari.com       | Bangalore |
+----+---------+------+------------+---------------------+-----------+
2 rows in set (0.00 sec)


ANY

Get all employees whose age is greater than any of the employees who are staying in Trivendram.

mysql> SELECT * FROM employee WHERE age > ANY(SELECT age FROM employee where city="Trivendram");
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   36 |  87666.8700 | manju@sandesh.com   | Bangalore  |
|  6 | Sankalp |   38 |  87645.6700 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   40 |  12345.6700 | vadi@hari.com       | Bangalore  |
|  8 | Prasob  |   37 |  12345.6700 | prasob@sandesh.com  | Trivendram |
+----+---------+------+-------------+---------------------+------------+
5 rows in set (0.00 sec)
  

BETWEEN
Get all the employees whose age is between 35 and 40.

mysql> SELECT * FROM employee WHERE age BETWEEN 35 AND 40;
+----+---------+------+------------+---------------------+------------+
| id | name    | age  | salary     | mailId              | city       |
+----+---------+------+------------+---------------------+------------+
|  4 | Manju   |   36 | 87666.8700 | manju@sandesh.com   | Bangalore  |
|  6 | Sankalp |   38 | 87645.6700 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   40 | 12345.6700 | vadi@hari.com       | Bangalore  |
|  8 | Prasob  |   37 | 12345.6700 | prasob@sandesh.com  | Trivendram |
+----+---------+------+------------+---------------------+------------+
4 rows in set (0.00 sec)

IN
Get all employees whose age is 36 (or) 38 (or) 40.
mysql> SELECT * FROM employee WHERE age IN(36, 38, 40);
+----+---------+------+------------+---------------------+-----------+
| id | name    | age  | salary     | mailId              | city      |
+----+---------+------+------------+---------------------+-----------+
|  4 | Manju   |   36 | 87666.8700 | manju@sandesh.com   | Bangalore |
|  6 | Sankalp |   38 | 87645.6700 | sankalp@sankalp.com | Chenai    |
|  7 | Vadiraj |   40 | 12345.6700 | vadi@hari.com       | Bangalore |
+----+---------+------+------------+---------------------+-----------+
3 rows in set (0.00 sec) 


EXISTS
It is used in sub queries.

Syntax
WHERE EXISTS ( subquery );

Suppose, I had another table ‘employee_stock_purchase’, where employee can invest some percentage of salary in company stocks.

CREATE TABLE employee_stock_purchase(
  id int PRIMARY KEY,
  empId int,
  percentage int
);

INSERT INTO employee_stock_purchase VALUES(1, 1, 10);
INSERT INTO employee_stock_purchase VALUES(2, 3, 8);
INSERT INTO employee_stock_purchase VALUES(3, 7, 9);

mysql> CREATE TABLE employee_stock_purchase(
    ->   id int PRIMARY KEY,
    ->   empId int,
    ->   percentage int
    -> )
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employee_stock_purchase VALUES(1, 1, 10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee_stock_purchase VALUES(2, 3, 8);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee_stock_purchase VALUES(3, 7, 9);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee_stock_purchase;
+----+-------+------------+
| id | empId | percentage |
+----+-------+------------+
|  1 |     1 |         10 |
|  2 |     3 |          8 |
|  3 |     7 |          9 |
+----+-------+------------+
3 rows in set (0.00 sec)
Get all employees who are invested in company stocks.

mysql> SELECT * FROM employee emp WHERE EXISTS (SELECT * FROM employee_stock_purchase esp  WHERE emp.id = esp.empId );
+----+---------+------+-------------+------------------+-----------+
| id | name    | age  | salary      | mailId           | city      |
+----+---------+------+-------------+------------------+-----------+
|  1 | Hari    |   28 |  12345.6700 | hari@hari.com    | Bangalore |
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com | Hyderabad |
|  7 | Vadiraj |   40 |  12345.6700 | vadi@hari.com    | Bangalore |
+----+---------+------+-------------+------------------+-----------+
3 rows in set (0.00 sec)



Previous                                                 Next                                                 Home

No comments:

Post a Comment