-->

MySQL Subquery

Summary: In this chapter, we will show you how to use the MySQL subquery to write complex queries and explain the correlated subquery concept.

Introducing to MySQL Subquery

A MySQL subquery is a query that is nested inside another query means it may occur in SELECT clause, FROM clause and WHERE clause.
In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. we can use the comparison operators, such as >, <, =, IN, ANY, SOME, or ALL.
The inner query executes first before its parent query so that the results of the inner query can be passed to the outer query.

MySQL subquery within a WHERE clause

Let’s take a look at the following subquery that returns employees who locate in the offices in the India.
  • The subquery returns all branch ids of the offices that locate in the India.
  • The outer query selects the first name, last name and jobtitle of employees whose branch id is in the result set returned by the subquery.

We can use a subquery anywhere that we use an expression. Only, you must enclose a subquery in parentheses.

MySQL subquery with IN operator

Suppose that we want employee’s first name, last name and job profile who is working in Indian branches. The following MySQL query is returning same result in the result set,
1
2
3
4
5
SELECT firstname, lastname, jobtitle
FROM employees
WHERE branchid IN (SELECT branchid
                       FROM branches
                       WHERE country='India')

MySQL subquery with NOT IN operator

Using MySQL subquery with NOT IN operator we can achieve just opposite result of above query like employees data who is not working in India.
1
2
3
4
5
SELECT firstname, lastname, jobtitle
FROM employees
WHERE branchid NOT IN (SELECT branchid
                       FROM branches
                       WHERE country='India')

MySQL subquery with comparison operators

We can use comparison operators such as, =, >, <, etc., to compare a single value returned by the subquery with the expression in the WHERE clause.
For example, you can find customers whose payments are greater than the average payment using a subquery.
First, you use a subquery to calculate the average payment using the AVG aggregate function. Then, in the outer query, you query payments that are greater than the average payment returned by the subquery.
In this chapter, we have learnt how to use MySQL subquery to write more complex queries.
MySQL HAVING MySQL INSERT Statement

at
Labels: