<p>I've been working with databases for the last few years and I'd like to think that I've gotten fairly competent with using them. However I was reading recently about Joel's <a href="http://www.joelonsoftware.com/articles/LeakyAbstractions.html" rel="noreferrer"> Law of Leaky Abstractions</a> and I realised that even though I can write a query to get pretty much anything I want out of a database, I have no idea how the database actually interprets the query. Does anyone know of any good articles or books that explain how databases work internally?</p>

<p>Some specific things I'm interested in are:</p>

<ul>
<li>What does a database actually do to find out what matches a select statement?</li>
<li>How does a database interpret a join differently to a query with several "where key1 = key2" statements?</li>
<li>How does the database store all its memory?</li>
<li>How are indexes stored?</li>
</ul>

What does a database actually do to find out what matches a select statement?
DBs are using indexes(see below)
How does a database interpret a join differently to a query with several "where key1 = key2" statements?
Join Operations can be translated to binary tree operations by merging trees.
How does the database store all its memory?
memorymapped files
for faster access of their data
How are indexes stored?
Internally DBs are working with
B-Trees
for indexing.
This should be explained in greater details on wikipedia..
http://en.wikipedia.org/wiki/B-tree
http://en.wikipedia.org/wiki/Database