Experiment No :6 Date: / / 2023
Title: Sub queries
Aim: To study the implementation of sub queries
Theory:
A MySQL subquery is a query nested within another query such as SELECT, INSERT, UPDATE or DELETE.
In addition, a subquery can be nested inside another subquery.
A MySQL subquery is called an inner query while the query that contains the subquery is called an outer
query. A subquery can be used anywhere that expression is used and must be closed in parentheses.
The following query returns employees who work in offices located in the USA
In this example:
The subquery returns all office codes of the offices located in the USA.
The outer query selects the last name and first name of employees who work in the offices whose
office codes are in the result set returned by the subquery
59
When the query is executed, the subquery runs first and returns a result set. Then, this result set is used
as an input for the outer query.
MySQL subquery in WHERE clause
We will use the table payments in the sample database for the demonstration.
MySQL subquery with comparison operators
You can use comparison operators e.g., =, >, < to compare a single value returned by the subquery with
the expression in the WHERE clause.
For example, the following query returns the customer who has the maximum payment.
In addition to the equality operator, you can use other comparison operators such as greater than ( >),
less than( <).
For example, you can find customers whose payments are greater than the average payment using a
subquery:
60
In this example:
First, use a subquery to calculate the average payment using the AVG aggregate function.
Then, query the payments that are greater than the average payment returned by the subquery
in the outer query.
MySQL subquery with IN and NOT IN operators
If a subquery returns more than one value, you can use other operators such as IN or NOT IN operator in
the WHERE clause.
See the following customers and orders tables:
61
For example, you can use a subquery with NOT IN operator to find the customers who have not placed
any orders as follows:
MySQL subquery in the FROM clause
When you use a subquery in the FROM clause, the result set returned from a subquery is used as
a temporary table. This table is referred to as a derived table or materialized subquery.
The following subquery finds the maximum, minimum and average number of items in sale orders:
MySQL correlated subquery
In the previous examples, you notice that a subquery is independent. It means that you can execute the
subquery as a standalone query, for example
62
Unlike a standalone subquery, a correlated subquery is a subquery that uses the data from the outer
query. In other words, a correlated subquery depends on the outer query. A correlated subquery is
evaluated once for each row in the outer query.
In the following query, we select products whose buy prices are greater than the average buy price of all
products in each product line
The inner query executes for every product line because the product line is changed for every row.
Hence, the average buy price will also change. The outer query filters only products whose buy price is
greater than the average buy price per product line from the subquery.
Conclusion: Studied & implemented Sub queries.
63
