Answer has already been accepted, but I thought I'd throw this in:
It may help to think about the type of the data and where those types fit into an overall query. SQL queries can return essentially three types:
- A single scalar value
- A list of values
- A table of values
(Of course, a list is just a one-column table, and a scalar is just a one-value list.)
When you look at the types, you see that an SQL SELECT query has the following template:
SELECT scalar(s)
FROM table
WHERE boolean-scalar
If your function or subquery is returning a table, it belongs in the FROM clause. If it returns a list, it could go in the FROM clause or it could be used with the IN operator as part of the WHERE clause. If it returns a scalar, it can go in the SELECT clause, the FROM clause, or in a boolean predicate in the WHERE clause.
That's an incomplete view of SELECT queries, but I've found it helps to figure out where my subqueries should go.