FORUM SQL Habit Scenarios of using subqueries
All Questions Solved Unsolved
tks
about 3 years
SOLVED

Scenarios of using subqueries

Hello,

I have noticed that there are a lot of places in which people have used subquery within select/from/where or some other clauses. Can you please share some scenrios & guidelines around when to use a sub-query in which clause.

Kind regards, Tushar

REPLIES

makaroni4
almost 3 years Reply

Hi Tushar,

really great question, you’ve made me think why and how I’m using subqueries actually. 😃

💡 Maybe it’d be great if you share some examples and we can discuss them, for now here’s a summary.

I believe that nesting queries is a bad practice. Nesting makes a query harder to read and understand: you read the first query, then you need to shift your eyes and read the second one (keeping in mind what was in the first one) and so on. It makes you interpret a query like an SQL engine and in my experience it’s very tiring.

The worst case is when it’s also poorly formatted and then it’s a total mess. At that point I’d give an author feedback and ask to make an effort to make it readable. Otherwise why we should make an effort to read it? 🤔

The only case for a nested subquery is when it’s a one-liner. For example, when we’re counting relative percentages:

SELECT 
  country,
  COUNT(*) AS user_count,
  100.0 * COUNT(*) / (SELECT COUNT(*) FROM users) AS share
FROM users
GROUP BY 1  
ORDER BY 3 DESC
LIMIT 5

As you can see it doesn’t really add nesting and is still easy to understand. Although we can do the same thing without nested subquery:

SELECT 
  country,
  COUNT(*) AS user_count,
  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share
FROM users
GROUP BY 1  
ORDER BY 3 DESC
LIMIT 5

💡 I guess the second version looks a bit more complex with window functions magic, that’s probably why the 1st approach is a bit more popular.

CTEs FTW

I’m a big believer in Common Table Expressions.

First of all, they don’t add any nesting. They follow logical order in which you did your research and their names together with nice column aliases tell a story.

The quote I always keep in mind:

We write code for humans, not machines.

It means that before any performance optimization we should ensure that our queries are readable and our intention with them is clear to anybody who’d read them. That to me is a golden rule.

Try to keep this golden rule in mind next time you write or review someone’s query.

Hope it was helpful, Tushar 👋

WRITE A REPLY