A subquery is a SELECT statement within another statement. : Introduction « Subquery « MySQL Tutorial






A subquery must always appear within parentheses.

A subquery's outer statement must be one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.

mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> create table job (
    ->   id         int,
    ->   title      VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Toronto',  'Programmer');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York',  'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York',  'Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,'James',    'Cat',     '19960917',  '20020415', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into job (id, title) values (1,'Tester');
Query OK, 1 row affected (0.00 sec)

mysql> insert into job (id, title) values (2,'Accountant');
Query OK, 1 row affected (0.00 sec)

mysql> insert into job (id, title) values (3,'Developer');
Query OK, 1 row affected (0.00 sec)

mysql> insert into job (id, title) values (4,'Coder');
Query OK, 1 row affected (0.00 sec)

mysql> insert into job (id, title) values (5,'Director');
Query OK, 1 row affected (0.00 sec)

mysql> insert into job (id, title) values (6,'Mediator');
Query OK, 1 row affected (0.00 sec)

mysql> insert into job (id, title) values (7,'Proffessor');
Query OK, 1 row affected (0.01 sec)

mysql> insert into job (id, title) values (8,'Programmer');
Query OK, 1 row affected (0.00 sec)

mysql> insert into job (id, title) values (9,'Developer');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from job;
+------+------------+
| id   | title      |
+------+------------+
|    1 | Tester     |
|    2 | Accountant |
|    3 | Developer  |
|    4 | Coder      |
|    5 | Director   |
|    6 | Mediator   |
|    7 | Proffessor |
|    8 | Programmer |
|    9 | Developer  |
+------+------------+
9 rows in set (0.00 sec)

mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE id = (SELECT max(id) FROM job);
Empty set (0.02 sec)

mysql>
mysql>
mysql>
mysql> drop table job;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>








6.1.Introduction
6.1.1.A subquery is a SELECT statement within another statement.
6.1.2.Using Join in the subquery
6.1.3.Using the aggregate function in subquery
6.1.4.Update with subquery
6.1.5.Using Having to subquery
6.1.6.Delete with subquery
6.1.7.Get the Row Holding the Maximum of a Certain Column with sub query in where clause
6.1.8.The Rows Holding the Group-wise Maximum of a Certain Field
6.1.9.Comparisons Using Subqueries
6.1.10.Find all rows in a table containing a value that occurs twice in a given column
6.1.11.Subqueries in the FROM clause
6.1.12.NULL Subquery with gradter than (>)
6.1.13.A scalar subquery can be part of an expression
6.1.14.The Subquery as Scalar Operand