
Advanced SQL and database books and resources - eatonphil
https://www.neilwithdata.com/advanced-sql
======
branko_d
If you are an application programmer needing to query the database, I
wholeheartedly recommend "SQL Performance Explained" by Markus Winand, or his
website: [https://use-the-index-luke.com/](https://use-the-index-luke.com/)

~~~
etiennebch
that was eye-opening indeed. Before reading his blog, I had no clue how
indexes worked and how to write SQL that match the index structure (for
instance the order of conditions in the where clause matter if you want to
leverage a multi-column index).

~~~
MarkusWinand
> for instance the order of conditions in the where clause matter if you want
> to leverage a multi-column index

Ups, if this is you take away, then I've done something wrong.

Let me correct that: The order of columns in an __index __matters, not the
order of conditions in the where clause.

~~~
therealdrag0
And IIRC this holds MongoDB and I'd assume other non-SQL DBs.

If you have an index on <companyId, userId>, and you query with a userID only,
that index wont be used. But if the index was <userId, companyId> then that
index would be used. Or if you supplied both userId and companyId in your
query, then either index would work.

~~~
JoshuaDavid
And yes, this occasionally means that adding a

    
    
        where id in (select id from company)
    

sometimes will switch your query from doing a full table scan to using an
index, fixing your problem for long enough to prepare a fix to add the
appropriate index. Not that I've ever had to do something like that or
anything.

------
rsecora
The PostgreSQL exercises web [1] is an awesome intro to basic and medium level
topics.

It has been discussed previously in HN [2].

[1] [https://pgexercises.com/](https://pgexercises.com/)

[2]
[https://news.ycombinator.com/item?id=12022953](https://news.ycombinator.com/item?id=12022953)

------
polskibus
I don't see Celko books

[https://en.wikipedia.org/wiki/Joe_Celko](https://en.wikipedia.org/wiki/Joe_Celko)

they're a must have resource for advanced SQL.

~~~
eatonphil
Do you suggest any one in particular? I've been daunted by their size.

~~~
nickpeterson
SQL for Smarties tends to have a nice coverage of common problems encountered
and how to resolve them.

~~~
arafa
That book was great and I learned a huge amount by reading it. But, unless
it's been updated (I read it years ago but it's also an older book), there
were numerous typos and even some code errors.

------
LunaSea
I couldn't dislike the marketing of "The Art of PostgreSQL" more ...

A website that looks like a SaaS.

Hugely overpriced paperback version.

No book summary available.

A free chapter banner that doesn't link anywhere.

No possibility to buy on different stores.

~~~
tarasmatsyk
I found the book apprisal somewhere on Reddit in "advanced books on postgres".
I expected to find postgres optimization and at least a few pages on how to
scale it (or where to look for solutions), instead I was dissapointed.

While it's still a very decent book on SQL syntaxes and advanced queries (I
bet even 10y+ PostgreSQL users don't know half of what PostgreSQL can do) here
is why I don't recommend it:

\- poorly written instructions on how to set up Postgres 11 with all those
crazy extensions

\- No epub version (it's simply broken and nobody responds on my emails,
instead I get a reminder to continue my learning course which has 2 links, a
short intro and a link to the book. I've stopped at 50% just because the book
was downloaded from another browser and now I get these irritating emails)

\- There is a lot of Python in the book, I want to learn SQL and PostgreSQL,
not Python or any other sort of languages. I use Py professionally but it does
not mean I want it to be tied to SQL.

\- There are unrelated interviews, for instance, a guy who worked on Django
ORMs (how is this related to my SQL knowledge)

Don't get me wrong, don't treat this comment as a rant about how bad the book
is, the SQL tricks are really awesome there, however if you think about buying
this book, go for 49$ version and expect to have some pain to setup
everything.

I am like 50/50 on asking for refund

------
ComodoHacker
If you want something _more_ advanced and fundamental, I'd recommend classic
Database System Implementation[0] by Hector Garcia-Molina; Jennifer D. Widom;
Jeffrey D. Ullman. It's still relevant. Technologies change, but fundamental
principles and algorithms... change order of magnitude slower.

And yes, it's _that_ Ullman.

0\.
[https://www.amazon.com/dp/0130402648](https://www.amazon.com/dp/0130402648)

------
zerkten
Rob Conery's A Curious Moon ([https://bigmachine.io/products/a-curious-
moon/](https://bigmachine.io/products/a-curious-moon/)) is an interesting read
for a PostgreSQL book. I wouldn't say it tries to be an advanced book by
design, but I learned a lot of modern SQL from it.

I think the number of people needing truly advanced books is small whereas a
lot of people need to jump from beginners to intermediate, or intermediate to
advanced.

~~~
rietta
Advanced PostgreSQL and NASA data ... sold. Shut up and take my $30. Instabuy
:-)

------
tuatoru
I have long thought that the best training for database is a course on set
theory and group theory, coupled with a close reading of William Kent's "Data
and Reality".

The book is a mess, but its structure illustrates its several points.

------
etiennebch
Cmu course is a must watch really

~~~
zerr
Yeah, it's good but somewhat diverged from the practical part unless you are
an onsite student.

I wonder what are alternatives (video lectures or textual resources) with more
hands-on approach implementing database systems.

~~~
etiennebch
Not sure if this is what you are looking for but I found this paper useful on
architecting databases. I read it as a complement to the CMU videos.
[https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf](https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf)

Otherwise there is surprisingly a lack of information about architecting
databases online and you have to go to the source code of SQLite and
PostgreSQL. I suspect this is because databases are some of the most complex
systems in computer science and involve many trade secrets.

~~~
SQLite
On-line docs describing the design and internal operation of SQLite include:
[https://sqlite.org/arch.html](https://sqlite.org/arch.html)
[https://sqlite.org/optoverview.html](https://sqlite.org/optoverview.html)
[https://sqlite.org/opcode.html](https://sqlite.org/opcode.html)
[https://sqlite.org/queryplanner.html](https://sqlite.org/queryplanner.html)
[https://sqlite.org/atomiccommit.html](https://sqlite.org/atomiccommit.html)

Inspired by this post, I started yet another:
[https://sqlite.org/draft/howitworks.html](https://sqlite.org/draft/howitworks.html)

------
fronofro
I am a bit biased here in terms of resources, I worked with a few people to
create a free resource that covers intro to sql optimization:
[https://dataschool.com/sql-optimization](https://dataschool.com/sql-
optimization)

I also created a bunch of animations to help people build mental models for
joins, aggregations, window functions, and subqueries which I find is the main
blocker for people using SQL optimally: [https://dataschool.com/how-to-teach-
people-sql/](https://dataschool.com/how-to-teach-people-sql/)

------
Pamar
A billion "Up" for _The Art of SQL_ by Faroult

------
roenxi
There is something to be said for having an advanced knowledge of SQL and then
carefully staying a very long distance from complicated queries. I can't think
of a language where the syntax buckles worse than SQL when things get
complicated.

Sometimes it makes sense to roll up the sleeves and do unspeakable things with
SQL, but that isn't some sort of win.

~~~
C1sc0cat
I recall seeing some gnarly SQL at British Telecom over 2k lines.

Id agree with you about keeping it as simple as possible

~~~
hobs
People do dumb things with SQL, but depending on the query/set of columns, 2k
is a pretty quick line count to reach. Just dont use select *, have a wide
table, do more than a few trivial operations moving data around, and you'll
get to 2k pretty quick.

That being said, people do dumb stuff with SQL all the time; The worst query I
have ever seen was 11MB, megabytes. The worst query plan I have ever seen
generated a plan that was ~130MB. I regularly have people ask questions in
#sqlhelp ([https://sqlps.io/slack/](https://sqlps.io/slack/) SQL Server
focused) like "Why can I only have 2100 parameters to a stored procedure?"
(dude was super mad that I asked if his design might use some rework.)

~~~
kthejoker2
I've told SQL learners before

There is no Advanced SQL; there's only "a very large amount of SQL", "exotic
SQL", and "SQL no one should have written."

------
beckingz
Really enjoy SQL Antipatterns by Bill Karwin.

