Writing Multiple Column Subqueries

The subqueries you ve seen so far have returned rows containing one column. You re not limited to one column: you can write subqueries that return multiple columns . The following example retrieves the products with lowest price in each product type group :

  SELECT product_id, product_type_id, name, price   FROM products   WHERE (product_type_id, price) IN   (SELECT product_type_id, MIN(price)   FROM products   GROUP BY product_type_id);  PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE ---------- --------------- ------------------------------ ----------  1 1 Modern Science 19.95  4 2 Tank War 13.95  8 3 From Another Planet 12.99  9 4 Classical Music 10.99 

Notice the subquery returns the product_type_id and the minimum value of the price column values. The outer query has a WHERE clause with the two columns product_type_id and price in parentheses.