Sub queries

Sub queries are queries inside queries. These are very useful in processing complex queries.

Suppose you had following employee data.

I am going to use following 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> 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)

Get highest salaried employee details.

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

Get 2^nd highest salaried employee details

                                            

Find highest salary

I am going to use following sample data.

mysql> SELECT * FROM employee;
+----+--------------+------------+------------+
| id | firstName    | lastName   | salary     |
+----+--------------+------------+------------+
|  1 | Hari Krishna | Gurram     |   12345.67 |
|  2 | Rama Devi    | Gurram     | 1234578.67 |
|  3 | Lakshmana    | Rao        | 9876543.67 |
|  4 | Rama         | Krishna    | 1234587.67 |
|  5 | Sowmya       | asd        | 1238745.67 |
|  6 | Jyotsna      | PS         |   76543.67 |
|  7 | Gireesh      | Amara      |   87698.00 |
|  8 | Sravani      | Nidamanuri |  987654.00 |
|  9 | Saranya      | Amara      | 1987654.00 |
| 10 | Sudhir       | Sami       |  876543.00 |
| 11 | Kiran        | Darsi      |   76549.00 |
+----+--------------+------------+------------+
11 rows in set (0.00 sec)

Approach1

SELECT MAX(salary) FROM employee;

mysql> SELECT MAX(salary) FROM employee;
+-------------+
| MAX(salary) |
+-------------+
|  9876543.67 |
+-------------+ 
1 row in set (0.01 sec)

Approach2
Use the ORDER BY clause in DESC order and get the first record.

mysql> SELECT * FROM employee ORDER BY salary DESC LIMIT 1;
+----+-----------+----------+------------+
| id | firstName | lastName | salary     |
+----+-----------+----------+------------+
|  3 | Lakshmana | Rao      | 9876543.67 |
+----+-----------+----------+------------+
1 row in set (0.00 sec)

Get all employees whose salary is less than maximum salary

                                          

I am going to use following sample data.

mysql> SELECT * FROM employee;
+----+--------------+------------+------------+
| id | firstName    | lastName   | salary     |
+----+--------------+------------+------------+
|  1 | Hari Krishna | Gurram     |   12345.67 |
|  2 | Rama Devi    | Gurram     | 1234578.67 |
|  3 | Lakshmana    | Rao        | 9876543.67 |
|  4 | Rama         | Krishna    | 1234587.67 |
|  5 | Sowmya       | asd        | 1238745.67 |
|  6 | Jyotsna      | PS         |   76543.67 |
|  7 | Gireesh      | Amara      |   87698.00 |
|  8 | Sravani      | Nidamanuri |  987654.00 |
|  9 | Saranya      | Amara      | 1987654.00 |
| 10 | Sudhir       | Sami       |  876543.00 |
| 11 | Kiran        | Darsi      |   76549.00 |
+----+--------------+------------+------------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);
+----+--------------+------------+------------+
| id | firstName    | lastName   | salary     |
+----+--------------+------------+------------+
|  1 | Hari Krishna | Gurram     |   12345.67 |
|  2 | Rama Devi    | Gurram     | 1234578.67 |
|  4 | Rama         | Krishna    | 1234587.67 |
|  5 | Sowmya       | asd        | 1238745.67 |
|  6 | Jyotsna      | PS         |   76543.67 |
|  7 | Gireesh      | Amara      |   87698.00 |
|  8 | Sravani      | Nidamanuri |  987654.00 |
|  9 | Saranya      | Amara      | 1987654.00 |
| 10 | Sudhir       | Sami       |  876543.00 |
| 11 | Kiran        | Darsi      |   76549.00 |
+----+--------------+------------+------------+
10 rows in set (0.01 sec)

 

Previous                                                 Next                                                 Home