These
are used to create complex queries.
Previous
Next
Home
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.
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)
No comments:
Post a Comment