Sub Queries

Subquery is  a query inside a query :
SO let there be two different tables one called orders from where we need the customer id with freight over 100 and let there be another table called customers where the details of the customers are stored .

>>select customerid,customername from customer where customerid in (select customerid from orders where freight>100);

--there is no limitations to number of subqueries in a statement but it may decrease the performance.
--subquery selects can only retrieve single columns.

subquery in subquery :
>>SELECT customer_name

 ,customer_contact
FROM customers
WHERE cust_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_number IN (
    SELECT order_number
    FROM orderItems
    WHERE prod_name = 'toothpaste'
    )
  );

more complex example :

>>SELECT customer_name
 ,customer_state
 ,(
  SELECT count(*) AS orders
  FROM orders
  WHERE orders.customer_id = customer.customer_id
  ) AS orders
FROM customers
ORDER BY customer_name