Home » , » MySQL Subqueries?

MySQL Subqueries?

05:07

You can use the result of a query like you use a list of values with the IN operator to filter a query based on the result of another query. The subquery appears in parentheses after the IN keyword.
The following query fetches all columns from the products table, but only for those product codes that were part of order number 1:

mysql> SELECT *
    -> FROM products
    -> WHERE product_code IN (
    ->   SELECT product_code
    ->   FROM order_lines
    ->   WHERE order_id = 1
    -> );
+--------------+---------------+--------+--------+
| product_code | name          | weight | price  |
+--------------+---------------+--------+--------+
| MINI         | Small product |   1.50 |  5.99  |
| MAXI         | Large product |   8.00 | 15.99  |
+--------------+---------------+--------+--------+
2 rows in set (0.00 sec)
Categories: ,