You need to spend time working with the language and understanding how the set based operations used in SQL work. Declarative languages means you can express things in multiple ways and get the same results. This is an incredibly important aspect of SQL
>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.