How about the following:
- 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.
- 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.