StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Joins are just ways of combining tables. Joining three tables is no different than joining 2... or 200. You can mix and match INNER, [LEFT/RIGHT/FULL] OUTER, and even CROSS joins as much as you want. The only difference is which results are kept: INNER joins only keep rows where both sides match the expression. OUTER joins pick an "origin" table depending on the LEFT/RIGHT/FULL specification, always keep all rows from the origin table, and supply NULL values for rows from the other side that don't match the expression. CROSS joins return all possible combinations of both sides.</p> <p>The trick is that because you're working with declarative code rather than more-familiar iterative, the temptation is to try to think of it as if everything happens at once. When you do that, you try to wrap your head around the entire query and it can get confusing.</p> <p>Instead, you want to think of it as if the joins happen in order, from the first table listed to the last. This actually is not how it works, because the query optimizer can re-order things to make them run faster. But it makes building the query easier for the developer.</p> <p>So with three tables, you start with your base table, then join in the values you need from the next table, and the next, and so on, just like adding lines of code to a function to produce the required output.</p> <p>As for using the different join types, I've used all the different types I listed here: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS. But most of those you only need to use occasionally. INNER JOIN and LEFT JOIN will cover probably 95% or more of what you want to do.</p> <p>Now let's talk about performance. Often times the order you list tables is dictated to you: you start from <code>TableA</code> and you need to list <code>TableB</code> first in order to have access to columns required to join in <code>TableC</code>. But sometimes both <code>TableB</code> and <code>TableC</code> only depend on <code>TableA</code>, and you could list them in either order. When that happens the query optimizer will usually pick the best order for you, but sometimes it doesn't know how. Even if it did, it helps to have a good system for listing tables so you can always look at a query and know that it's "right". </p> <p>With that in mind, you should think of a query in terms of the <code>working set</code> currently in memory as the query builds. When you start with <code>TableA</code>, the database looks at all the columns from <code>TableA</code> in the select list or anywhere else (like WHERE or ORDER BY clauses, or potential indexes) in the query, factors in relevant conditions from the WHERE clause, and loads the smallest portion of that table into memory that it can get away with. It does this for each table in turn, always loading as little as possible. And that's the key: you want to keep this working set as small as possible for as long as possible.</p> <p>So, going back to our three-table join, we want to list the tables in the order that will keep the working set smaller for longer. This means listing the smaller table above the larger one. Another good rule of thumb is that INNER joins tend to <em>shrink</em> result sets, while OUTER joins, tend to <em>grow</em> result sets, and so you want to list your INNER joins first. However, this is not a <em>requirement</em> for a query to work, nor is it always true; sometimes the reverse can happen as well.</p> <p>Finally, I want to point out again that this isn't how it really works. The query optimizer and execution plans are a very complex topic, and there are lots of tricks the database can take that break this model from time to time. It's just one model that you as a developer can use to help understand what the server is doing and help you write better queries.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload