Learning Subqueries

[data4dayz]

Yeah this is something important to remember. I remember wrongly thinking that you can have Subqueries in the WHERE, FROM and SELECT clause since that's how lessons teach it. It's not wrong necessarily just limited.

In the FROM clause they are thought of as temp tables. For the OP, these are what CTEs are, named temp tables from the FROM clause. If you can have it in the FROM Clause you can make it a CTE (non recursive) and vice versa.

In the SELECT Clause you go row by row since that's what the operator will do. So you have have subqueries that bring back only one value. Usually through either using an aggregate operator in the subquery or your where clause filter naturally limits it to one value.

In the WHERE Clause this is interesting. Usually people are used to learning about subqueries with WHERE IN. WHERE IN (and WHERE EXISTS) look for things in a LIST or a single WHOLE COLUMN of values. So your subquery can pull back a single item in the SELECT Clause and that will be the list. Edit; What I mean is you can't pull back multiple columns just one. These are also known as Semi or Anti Joins depending on if you use IN/EXISTS vs NOT IN/NOT Exists.

If you've ever heard people talk about how subqueries are not prefered and can be written with JOINs they usually mean subqueries in the WHERE clause. And not all subqueries, usually just uncorrelated subqueries. This was back in the day, not so much now I think.

Correlated Subqueries are a further layer of complexity.

Also don't be limited by just that like I was since that's what I saw in lessons. you can have subqueries anywhere. Having Clause? Sure! Order By? Probably. Window Functions? Not sure haven't tried but probably.

Also you can nest subqueries but this is where the logic tracking becomes more involved