MySQL Sub Query

 

A sub query is a query within a query. A subquery can take place in a SELECT, FROM, and WHERE clause. A subquery can be used in a INSERT, UPDATE, SELECT, and DELETE query.

The main query is called the outer query and the subquery in called the inner query.

The sub query is executed and its results are passed to the outer query which is then executed after.

SELECT 
    column_name1, column_name2,....
FROM
    table_name1
WHERE
    column_name1 operator (SELECT 
            column_name
        FROM
            table_name2
        );

Here we are filter out rows with sales_rep_id that is less than the three smallest sales_rep_id which is, in this case, 3, 4, and 5.

Subquery in FROM clause

Subquery in the FROM clause forms a derived or temporary table. This derived or temporary table must have an alias.

As you can see, “(SELECT phone, credit_limit FROM customer) as tempCustomer” formed a derived table.

Correlated subquery

A correlated subquery is different from a regular subquery that stands alone. A correlated subquery uses columns or data from the outer query. It evaluates once for every row in the outer query.

Here is another example where we select all customers with address id that is greater than the average id of the employee.

 




Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


Leave a Reply

Your email address will not be published.