How does one get this level of SQL proficiency? I am not sure how to go beyond my current level of understanding, and these queries don't make much sense to me. I don't often do more than some selects and maybe an inner join occasionally, so far.
I got to my current level of understanding by (a) writing 1000 queries as part of my college degree; we got to define our own schema, but then were given a list of query features to use, and several months to write them for our schema. This was perhaps 20% of the score for our databases class. I know of no better way than exercises of gradually increasing complexity. Even the school of experience is less complete and may have inconsistent difficulty jumps that leave you stuck.
And (b) exhaustive examination of query plans, while having mental models of what btrees and hash maps are. To understand performance and adjust your query to encourage a different plan, you need to understand what's possible and develop an intuition about what the database will choose to do.
I write fairly large SQL programs composed from successive queries in order to process large inputs (millions of rows) efficiently. It's cheaper to load the data into the database and compose operations over it than to loop in memory and iteratively perform queries.
You're focused on what you know you don't know. You don't have a performance problem, so is the information worth acquiring if you don't have a problem? Maybe, maybe not.
What you might not know is ways to use the data in your database to make your application simpler or dramatically faster, not because it's a problem now, but because you don't know that there's a way it could be much faster or better. It could be reporting or some other kind of statistical output, something around import or export, or some features that could be possible that you simply didn't think were possible.
IMO relational models are usually superior to object oriented models. Much pain comes from trying to fit a relational model into an OO skin. Once tightly wrapped up in OO, a lot of the power of the relational model becomes hidden. OO turns into a straitjacket for your data.
I do agree that understanding the relational model is hugely valuable, compared to just retrieving and converting rows to 'objects' and going from there (with, at most, some basic WHERE clause).
From my experience so far, simply moving beyond the most basic queries is already so much of a win that in most situations that are not 'web scale', it is more than enough. Especially considering the number of pretty successful companies I've seen that don't even go beyond the ORM basics.
Basically, (b) strikes me as an argument similar to, say, dropping down to assembly when in tons of cases just sticking with dog-slow Ruby is a common and legit approach to many use cases where performance isn't a serious bottleneck.
It's essential in principle because RDBMSs are not magic, but are asked, as an application grows, to do increasingly magical things. Understanding how, and more importantly why the DB chooses to execute your query in a given way is absolutely key to understanding how to design data flows and distribution across the non-DB parts of your application. The people who designed your RDBMS are smarter than you; the query planner is (albeit limited) insight into how they applied their intelligence rather than just how you benefit from it. Understanding that databases are collections of variously-traversed data structures underneath, chosen carefully to balance multiple use cases, can lead you to better understanding of other code you write.
It's essential practically because without consistent discipline and coordination (things that diminish, typically, as companies and codebases grow), queries and schemas tend to grow over time. Even outside of the complex reporting use cases, it's not uncommon to have really big/weird queries running on a very high utilization production database. Those queries interact with other stuff that's running in sneaky ways, causing everything from slowdowns to absolute deadlocks, and understanding exactly what is breaking them requires a thorough understanding of how the query plan works. "Add indexes and don't use the DB as a message queue" is excellent advice, and will get you very far, but past a certain size/cruft level, you need to go deeper.
It's essential career-wise because every company with more than a handful of engineers has a database guy or gal, and that person is typically considered essential and worthy of lucrative reward--even in environments where other backend engineers are considered replaceable cost centers. This is usually not the DBA, but an application developer who really deeply understands how the queries from your code hit the database, how it behaves if more than one of them run at the same time, and how to fix it if it breaks. A DBA debugging an unfamiliar/new query has to start from closer to first principles: how is it broken, how to tweak it to fix it, how to isolate it, etc. The database guy starts from the other end: is the code that generated it sane, given the data access patterns it's requesting? The querying code is a solution to whatever problem its writer was trying to solve, but is it the right solution? The query planner and database statistics/activity views are this person's bread and butter.
It's only O(n^2) if your DBMS uses nested loops to do this join. If it uses a merge join it's O(n log n) (plus you get an order by for 'free'), and if it uses a hash join it's O(n).
MySQL, last I checked, only supported various forms of nested loops, but first-class databases like PostgreSQL support the others.
http://www.postgresonline.com/journal/archives/27-Reading-Pg... (example pictures if anyone reading this hasn't seen it before), it is pretty cool.
Also, books are far better than any online tutorial. I'd suggest doing the database before reading the books, as that will show you all the mistakes fresh in your memory.
And don’t use ORMs.
For Postgres and other feature rich RDBMS's one important thing is to take some time to learn what's possible. generate_series() is at the heart of a lot of good reporting tricks, as shown in the article.