365 Days of Daily Coding: Day 116

I happened to come across a superb way to boost your linkedin profile. Do follow wonsulting’s resources on how you can get your dream job. I absolutely love their resources. Here is the link to their website.

As for the question, it is a bit tricky and not an easy one to answer.

When subqueries are a bad idea:

• Based on the quora answers here, subqueries are a bad idea when it is used as a correlated subqueries and there are large number of rows returned by the parent query. In these instances, correlated subqueries execute once for each row returned by the parent query. For example, when the parent query has 1000 rows, the correlated query also runs each time for the 1000 rows. This can be taxing performance wise and it is best to use other methods that could save the performance.

#example of a correlated or dependent subquery
 SELECT employee_number, name
   FROM employees emp
   WHERE salary > (
     SELECT AVG(salary)
       FROM employees
       WHERE department = emp.department);

Here, the subquery is correlated with a column of the outer query, therefore, it must be re-executed for each row of the result. (WHERE department = emp.department)

When subqueries are a good idea:

• Subqueries are a good idea to use when there is no need to run dependent queries. Unlike correlated or dependent queries, independent queries will only be evaluated once.

#example of an independent query
SELECT employeeid, firstname, lastname
     FROM employees
          WHERE employeeid IN (SELECT DISTINCT employeeid 
          FROM employees);

The trick to identify whether a subquery is an independent or dependent is by testing whether the subquery can be run on its own without the parent or outer query.

P/S: Yay! for a successful post after a long hiatus. I will be continuing my Advanced SQL series followed by Serious SQL. For those of you getting ready for interviews or looking for one, good luck. May the universe be with you.