I fucking suck at SQL and that's hurting my performance as a supposed backend developer :D

All the cool nested queries and whatnot, it just flies over my head, I need to learn it better, any tips on study resources?

https://mystery.knightlab.com/

😐

Just google "sql execution plan" and "sql query tuning".
In my personal experience - it is always better to join two subquerys results over joining two tables, and then query the join.
OFC - it depends on your specific use case.

Most of what you can find in StackOverflow is wrong.

Most of what people are telling you is wrong.

@magicMirror sorry, but when I understand you correctly, you say joining the result of 2 generated tables in memory as a result of subqueries is better than 2 JOINs? That sounds very wrong to me, too.

So... What to do...

It's rather trivial.
SQL knowledge consists of two things:
1)SQL language and visualization.
2)SQL RDBMs specifics aka knowlege of the guts.

1)
SQL language is a standard - while there are some differences depending on RDBMs, the "core logic" behind an SQL is still the same.
E.g. MS SQL
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY works the same like MySQL
LIMIT 10.
Different syntax, same outcome.

What most people struggle with is visualization. Aka realizing what an query actually does *inside* the RDBMs.

Which leads most often to very bad queries, cause they apply any solution they can find without realizing the consequences of their solution.

One of the most common antipatterns is (not to piss magicMirror off, it's just the way it is)... The terrible misconception of "joins are bad".

Execution plans help to a certain degree understanding what's happening, though I really recommend working on understanding what an SQL query does vs getting the desired result by formulating a possible query.

"Joins are bad" - the most funny thing I've experienced are people who e.g. constantly use LEFT JOINs. Given that an LEFT join always joins two tables without excluding non matching rows, it's logical that its Performance is e.g. worse than an INNER join. :-)

https://use-the-index-luke.com/

Is e.g. a very good resource for getting the basic stuff right.

2) the inner guts

Intermediate level plus. Many RDBMs servers have certain strength and weaknesses. Understanding the query engine is advanced stuff, but can make a huge difference. E.g. Postgres has not so much configuration, but a lot of "toys" available instead.

@IntrusionCM

'''
select * from
(select * from A where. ...) as T1
left join
(select * from B where. ...) as T2
on T1.pk == T2.fk
where ....
'''

This pattern had better performance then joining A and B directly. Especially when the sub query results count <<< A count or B count. ofc - this depends on your sql database software optimizations.

as a rule of thumb: each time you add a layer of nested queries, re-evaluate what you're doing (with your code or your life, your choice) and check if there isn't a simpler method. because in my experience in 99% of cases, there is.

also: ORMs exist.

@tosensei
Very true.