1

1.I would like to know when do we use the structure of sub-queries in the select clause?

2.If any query which is written in 1. structure has an equivalent in one of the other nested forms:

  SELECT ... from ... where .. IN (SELECT ...)
    SELECT ... from (SELECT ...)

Thanks

JavaSa
  • 5,813
  • 16
  • 71
  • 121

1 Answers1

4

For the purposes of this answer, I will refer to the first select as the outer select, and the second select as the inner select.

You would use SELECT ... FROM ... WHERE ... IN (SELECT ...) if you want to find records that a similar to a record you know about. Items from the inner select can be compared to the outer select.

Get the population of cities which are in Texas
SELECT city, population FROM cities WHERE city IN (SELECT city FROM states WHERE state = 'Texas')

You would use SELECT ... FROM ... (SELECT ...) if you want to create a derived table for your statement. Items calculated on the inner select can then be used on the outer select.

Get employees who have earned more than $500 this week (derived table x)
SELECT name, ROUND(wages) FROM (SELECT name, per_hour * hours_worked AS wages FROM timesheet) AS x WHERE wages > 500

There is also a third way which is a correlated subquery, which would be displayed as SELECT ... FROM ... WHERE ... (= or > or < or != etc) (SELECT ...) and is used when you want to select rows based on a calculation.

Get cities with bigger populations than 2 times the average
SELECT city, population FROM cities WHERE population > 2 * (SELECT AVG(population) FROM cities)
sjdaws
  • 3,466
  • 16
  • 20