SQlLSUBQUERY Note 2

Introduction to the MySQL Subquery

A MySQL subquery is a query nested within another query such as SELECT, INSERT, UPDATE or DELETE. Also, a subquery can be nested within another subquery.

A MySQL subquery is called an inner query whereas 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.

For example, the following query uses a subquery to return the employees who work in the offices located in the USA.

SELECT
    lastName, 
    firstName
FROM
    employees
WHERE
    officeCode  IN 
        (SELECT officeCode
        FROM offices
        WHERE country = 'USA'
        );

The provided SQL code is a query that retrieves the last names and first names of employees who work in offices located in the USA.

The query is divided into two parts: the main query and a subquery.

The main query is:

SELECT
    lastName, firstName
FROM
    employees
WHERE
    officeCode
    IN

This part of the query is selecting the lastName and firstName columns from the employees table where the officeCode is in a list of values.

The list of values is generated by the subquery:

(SELECT
officeCode
FROM
    offices
WHERE
    country = 'USA')

This subquery is selecting the officeCode from the offices table where the country is 'USA'. The result of this subquery is a list of office codes that are located in the USA.

The IN operator in the main query then checks if the officeCode for each row in the employees table is in this list of office codes. If it is, that row is included in the result set.

So, in summary, this query is retrieving the last names and first names of all employees who work in offices that are located in the USA.

Independent Sub-Query

Correlated Sub-Query