Subqueries - 96ankur/SQL GitHub Wiki

Subqueries

• It is a SELECT statment within anyother query.

  SELECT *
  FROM employees
  WHERE salary > (
  	SELECT AVG(salary)
      FROM employees
  )
  

• This is first syntax of writing subquery.

Subquery with IN operator

•   SELECT *
    FROM clients
    WHERE client_id NOT IN(
    	SELECT DISTINCT client_id 
        FROM invoices
    )
  

Subquery vs Joins

• With subquery

  SELECT customer_id, first_name, last_name
  FROM customers
  WHERE customer_id IN(
  	SELECT DISTINCT customer_id
  	FROM orders
  	WHERE order_id IN (
  		SELECT order_id
  		FROM order_items
  		WHERE product_id = 3
  	)
  )
  

• With joins

  SELECT DISTINCT customer_id, first_name, last_name 
  FROM customers c
  JOIN orders o
  		USING (customer_id)
  JOIN order_items oi
  		USING (order_id)
  WHERE product_id=3
  

ALL clause

• SELECT * 
  FROM invoices
  WHERE invoice_total > ALL (
  		SELECT invoice_total
  		FROM invoices
  		WHERE client_id = 3
  )
  

ANY clause

•   SELECT *
    FROM clients
    WHERE client_id = ANY (      -- in place of = ANY, IN operator can all be used
    		SELECT client_id
    		FROM invoices
    		GROUP BY client_id
    		HAVING COUNT(*) >= 2
    )
  

Correlated Subquery

• In correlated subquery, the subquery is executed for each record of the outer query.

  SELECT *
  FROM employees e
  WHERE salary >(
  		SELECT AVG(salary)
  		FROM employees
          WHERE office_id = e.office_id
  )
  

Exists operator

•   SELECT client_id, name 
    FROM clients
    WHERE client_id IN (
    	SELECT DISTINCT client_id 
        FROM invoices
    );
  
  

• The above query should only be used when the result of the subquery is less. But if the result of the subquery is very large then this query leads to performance issues. In that case we can use EXIXTS operator

•   SELECT client_id, name 
    From clients c
    WHERE EXISTS (
    	SELECT client_id 
        FROM invoices
        WHERE client_id = c.client_id
    );
  

Subquery in SELECT clause

• SELECT client_id,
     name,
     (SELECT SUM(invoice_total) 
  		FROM invoices
  		WHERE client_id = c.client_id)AS total_sales,
     (SELECT AVG(invoice_total)FROM invoices) AS average,
     (SELECT total_sales - average) AS difference
  FROM clients c
  

Subquery in FROM clause

• Useful only for simple queries.

  SELECT * 
  FROM (
  SELECT client_id,
  	   name,
         (SELECT SUM(invoice_total) 
  			FROM invoices
  			WHERE client_id = c.client_id)AS total_sales,
         (SELECT AVG(invoice_total)FROM invoices) AS average,
          (SELECT total_sales - average) AS difference
  FROM clients c
  ) AS sales_summary   -- this alias is mandatory
  WHERE total_sales IS NOT NULL
  

IFNULL and COALESCE function

• SELECT CONCAT(first_name, last_name) AS customer,
     IFNULL(phone, "Unknown"),
     COALESCE(phone, "Unknown") -- it can take 3 parameters
  FROM customers;
  

IF function

• SELECT *,
  	   IF(orders > 1, "Many times", "Once") AS frequency
  FROM (
  	SELECT product_id,
  		   name,
  		   COUNT(product_id) AS orders
  	FROM order_items oi
  	JOIN products p USING (product_id)
  	GROUP BY product_id, name
  ) AS product_data
  

CASE operator

• SELECT CONCAT(first_name, last_name) AS customer,
  	   points,
         CASE
  			WHEN points > 3000 THEN 'Gold'
              WHEN points >= 2000 THEN 'Silver'
              ELSE 'Bronze'
  		END AS category
  FROM sql_store.customers
  ORDER BY points DESC