How to do sub query on MySQL

Sub query is method of using select statement within another statement, so basically you can have multiple select inside your regular query.

Sub query probably little bit confusing at first, specially for a beginner like me, but once you know how to do it, it's actually quite easy to do.

The best way to learn sub query is using an example, so for this tutorial i'm going to create three tables for demonstration purpose.

The data that i'm going to use on this tutorial, may not give you the idea of why you need to use sub query, but my goal is just to show you how to do sub query that's all.

Here's the three tables that i'm going to use for this tutorial:

CREATE TABLE IF NOT EXISTS `employee` (
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `employee_gender` char(1) NOT NULL DEFAULT 'u',
  `employee_status` varchar(8) NOT NULL DEFAULT 'active'
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `gender` (
  `gender_id` int(11) NOT NULL,
  `gender` char(1) NOT NULL DEFAULT 'u',
  `gender_name` varchar(7) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `status` (
  `status_id` int(11) NOT NULL,
  `status_name` varchar(8) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

And here's the dummy data for each tables:

INSERT INTO `employee` (`employee_id`, `employee_name`, `employee_gender`, `employee_status`) VALUES
(1, 'john', 'm', 'active'),
(2, 'smith', 'm', 'active'),
(3, 'jane', 'f', 'active'),
(4, 'michael', 'm', 'deleted'),
(5, 'lee', 'f', 'active'),
(6, 'brandy', 'u', 'active'),
(7, 'melisa', 'f', 'inactive'),
(8, 'bradley', 'u', 'deleted');

INSERT INTO `gender` (`gender_id`, `gender`, `gender_name`) VALUES
(1, 'm', 'male'),
(2, 'f', 'female'),
(3, 'u', 'unknown');

INSERT INTO `status` (`status_id`, `status_name`) VALUES
(1, 'active'),
(2, 'inactive');

As you can see those three tables are not related by id, but we use sub query to get result from combination of different tables.

Here's sample of sub query :

SELECT 
    e.employee_name AS my_employee, g.gender_name AS my_gender
FROM
    (
	(SELECT * FROM employee) AS e, 
        (SELECT * FROM gender) AS g
    )
WHERE
    e.employee_gender = 'm' AND
    g.gender_name = 'male'

The above query shows sub query on 'FROM' statement, but you can also placed sub query on other place such as on 'WHERE' statement, like this:

SELECT 
    employee_name, employee_status
FROM
    employee
WHERE
    employee_status = (SELECT status_name FROM status WHERE status_id = 1) AND
    employee_name != 'brandy'

You can also place sub query under the 'SELECT' statement itself, so you can have select inside another select and so on, here's the example:

SELECT 
    status_name,
    (SELECT gender_name FROM gender WHERE gender_name = 'female') AS gender,
    (SELECT employee_name FROM employee WHERE employee_id = 3) as employee
FROM
    status

Sub query is very important aspect of SQL, as it makes the database to be structured properly the way the developer want.

Sub query also provides alternative for more complex statement such as JOIN and UNION, i will discuss about JOIN and UNION on the next article.