When you look at SQL from a logical/set-based perspective, it is by no means unintuitive. Basically, all you do is join all the tables you need and then filter out everything you don't need and maybe do an aggregation here and there.
- When to use JOIN vs a subquery?
- When is a subquery actually a correlated subquery? Will this destroy your performance? Or is it a critical feature?
- Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?
- When do you use WHERE vs HAVING?
- Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?
Etc. etc. The basic concepts are simple, but the implementation details quickly become very complex, particularly when you're ensuring high performance with indices, and making sure the query uses the indices.
And all of the questions I pose above have clear answers... but the answers certainly aren't obvious from SQL basic concepts.
And also, while JOIN seems like it ought to be intuitive, in real life it seems like it's like pointers in C -- some people get it pretty quickly, other people struggle forever.
Once you're at the point where you have to worry about these things, tuning the SQL is still probably much less complex than writing the query in your app language or figuring out how a NOSQL db can do these joins.
E.g., for a quick practice run, try optimizing Wordpress without making it a static page via caching - how many queries will you have to optimize and how much of a codebase rewrite will it be to make it significantly more performant?
the problem you describe is completely different and its more a measure of system health in its entirety of the DB. it can be easier to fix or harder depending on the precise cause. but that's a standard debugging skill which isn't strictly in the DBA skillset.
True, but those kinds of questions come up in every language: Should I use an array or a dictionary? Should people have references to projects or should projects have references to people or both? Is money a float, an int, a decimal or should I write my own money class? Should I memoize the results? Is it thread-safe?
As you can see, this could go on forever, pretty much for any language.
"Its easy if you think about it mathematically" is not enough.
There are simply things you have to learn to use a technology. If you want to write Java, you need to know what a variable is, what a for loop is and what Inheritance/Interfaces are. Likewise, in the case of SQL, it means you need to know concepts like normalization, ACID and joins. Just poking around until the code works won't do it.
Best thing you can do is to learn how to read EXPLAIN ANALYZE results.
Normally, subqueries return a single value whereas joins can result in n rows of output for 1 row being joined on, and you can access all the columns of those n rows. There are ways to make use of more than one value (e.g. (tuple) IN (subquery)) but if you want to SELECT more than one value, you need to join.
Depending on the database, it might be slower to do a correlated subquery than a join though (MySQL especially).
> - When is a subquery actually a correlated subquery? Will this destroy your performance? Or is it a critical feature?
A subquery is a correlated subquery when it references symbols from the outer query. That means it needs to be evaluated once per row, and can't be evaluated once at the start of query execution. It can destroy performance if it needs to be evaluated too often - if it's in your 'where' clause and is evaluated over too many rows, e.g. it's mixed in with a boolean expression that can't be short cut.
> - Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?
Conventionally, you should put equi-join constraints (equality expressions with foreign / primary keys in other tables) in the JOIN clause and other constraints in the WHERE clause. For inner joins, it doesn't make a difference where you put the predicate, semantically. There is a semantic difference for outer joins though (left join, right join, full outer join): failure to join results in a tuple worth of null values from one or both sides (left/right vs full), rather than eliminating the row.
Where the semantics aren't different, performance should not be affected. Of course the database engine might be stupid, but a fundamental requirement of a reasonable query planner is in determining (a) join order and (b) which indexes to use for the combination of join predicate and where predicate. No query planner worth its salt won't consider using the where clause along with the ON clause on the JOIN when fetching rows in the joined table.
> - When do you use WHERE vs HAVING?
WHERE is before GROUP BY and filters the rows that enter aggregation (if any), HAVING comes after GROUP BY and filters the aggregated rows. If you use a derived table (a nested query with a table alias), then you can use WHERE instead of HAVING for no semantic difference, but derived tables may execute differently (MySQL will generally materialize them, PostgreSQL will see through them).
> - Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?
If the column is nullable, and you used an outer join, you can't tell. Normally you check for the primary key or some other non-nullable column to discover if a join failed (most often used in anti-join, when you want to find all rows that don't have corresponding rows in the join).
>the basic concepts are simple, but the implementation details quickly become very complex
This is no different than programming. Assuming that SQL doesn't have complexity because you can only SELECT, INSERT, UPDATE or DELETE is going to have you banging your head against the wall. Tackle the complexity in SQL like you'd tackle the complexity in your programming language of choice; read the docs, work through examples, and read how other people solve the problem. There's ton out there for SQL
>When do you use WHERE vs HAVING?
HAVINGs allow you to add a condition to an aggregate function. So SUM(myColumn) > 5 would be something you put in a HAVING clause. Honestly, this is pretty clear cut.
>Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?
The first thing to understand is a condition in a join versus a where might return a different result set, specifically on anything other than an INNER join. The impact to performance will depend on the rest of your query, your data, and your index coverage. For simple cases, there is likely no difference. For complex ones, there may be an impact
> Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?
An inner join shouldn't produce a null. That's why it's an inner join, as the data needs to exist in both places. If you want to "test" whether a join found a row, look at the field you were joining to and see if it's a non-null value. Nulls won't join to Nulls unless you've change some settings in most RDBMS. If you're looking at other fields to determine the presence of a row from a join, make sure you're looking at a non-nullable field.
>When to use JOIN vs a subquery?
A better way to phrase this would be when to just join the table, vs writing a sub query and joining to that. When is a question of the complexity of the query and performance characteristics, and that can't be answered in the abstract. The most important thing is that in a large number of cases you can do both, and knowing how to express things in both ways is powerful.
>And all of the questions I pose above have clear answers
No they don't. Any time you're wondering about how different SQL impacts performance, there's absolutely a huge "it depends" angle on it, because how you've structured the tables, index coverage, and the volume of data, can have a significant impact. This is why DBAs still have jobs, because the database is an incredibly complex system. You seem to be complaining that SQL shouldn't be complex, yet are not willing to accept that it is more complex that you've assumed it to be. It's complex. You don't need to know everything if your just a dev, but don't just assume it's simple.
>while JOIN seems like it ought to be intuitive
I'd check the diagram here -> https://stackoverflow.com/questions/13997365/sql-joins-as-ve... Half those joins aren't needed as you can re-order a right join into a left join. For 95% of development Inner joins and left joins are all you need. The other 5% is an outer join and that's mainly needed in report writing, not app development.
This article is a great starting point:
https://blog.jooq.org/2016/03/17/10-easy-steps-to-a-complete...
there are many other articles worth reading on there
Relational algebra, which is what SQL is ultimately based on, is much more elegant.
Triggers, functions, procedures, access control.....