Adventures In Foreign Keys 5: How Join Elimination Makes Queries Faster


This is the last post I’ll write about foreign keys for a while. Maybe ever.

Let’s face it, most developers probably find them more annoying than useful, and if you didn’t implement them when you first started designing your database, you’re not likely to go back and start trying to add them in.

Depending on the size of your tables, and how much bad stuff has leaked in, it could be quite a project to fix the data, add the constraints, and then finally… Get a bunch of app errors, and realize why they weren’t there in the first place.

Now you’ve annoyed the developers. They’ll be coming with torches and pitchforks for the sa password.

But My Join Elimination!

Let’s start with known limitations!

  • Multi-key foreign keys
  • Untrusted foreign keys
  • If you uh… need a column from the other table

And sometimes, just sometimes, the optimizer will monkey your wrench. Here’s a funny example!

That’s Not Funny.

Here’s our constraint:

This query gets join elimination, because we followed all the rules.

Our constraint is trusted, it’s on one column, and we’re only referencing the Badges table in the important parts of the query: the select list and where clause.


We’re very excited. Very excited indeed about the prospects of join elimination.

We’re so excited that now we wanna write our query to only filter down to a range of users.

Now, if you look at that query, there are a whole lot of “b” aliases. I’m not asking for anything but a join to the Users table, just like before.

The last query got join elimination. This one doesn’t.


That’s an interesting choice, to say the least.

But I know what you’re thinking. Between is awful. Aaron said so.

How About Equality?

Let’s look for just one user. This’ll go better, right?

Not so much.

Throb Throb

About That Join Elimination…

Sometimes, even if you do everything right, it doesn’t work out. I’m not doing anything particularly tricky, here, either.

If a Bouncer-American can find this in a few minutes of messing around, imagine what you can come up with?

Thanks for reading!

Previous Post
Adventures In Foreign Keys 4: How to Index Foreign Keys
Next Post
[Video] Office Hours 2018/11/14 (With Transcriptions)

14 Comments. Leave new

  • Haven’t read the other FK articles, but I am wondering why you even need to join against the Users-table in these examples?

  • I had high hopes that this series of articles would finally convince me that Foreign Keys where a really good idea. But if anything I feel you have made the case that they are not worth the hassle they bring, especially if you are retrofitting them to an existing design. I get that there are downsides to not having them, but on balance I think we (my dev team) are better off without them. We have built many applications in our shop over the years some with FK some without. The applications with FK are a PIA for loading data. While the pain is obvious the benefits are not. I can’t recall an issue where we have said Gee I wish we had a FK on that table. Just my experience and 2 cents worth. Without trying to start any sort of religious war what have I missed?

    • Paul — no, nothing, unfortunately. I think they’re a bit like partitioning. If you’ve been using them from the get-go, they’re sweet as. If you’re trying to shoe horn some existing data in, it’s a herculean effort for not much return. I’d rather spend my time working on optimistic isolation levels or something, which would have more concrete benefits.

    • The issue with not having a FK on a table is that it makes it ever so easy to load bad data as well.

  • When I write queries with INNER JOINs, I ut as many conditions as possible into the ON clauses, and make the WHERE clause as lean as possible (if all the JOINs are INNER, I consider it a code smell for there to be a WHERE clause at all… yes I’m a full-stack dev, not a data dude). The earlier you can eliminate records, the more efficient the query.
    I know the example here is contrived to show the specific point, but I’d be surprised if the JOIN wasn’t eliminated in the query plan if that filer was moved to the ON clause.

  • Doh! Keep it simple v. premature optimization & simple wins! Shocking!

    Reminds me of my high school accounting teacher, who often referred to “the exception that proves the rule”, since I now realize this has been an exception in my book, but shouldn’t be (and won’t be any more) because it does follow the rule, the KISS rule! And I’m the Silly this time! 🙂

  • Mujungu Sabiti
    January 24, 2021 12:14 pm

    Hi can you help me

  • Joseph Daigle
    April 30, 2021 1:49 pm

    In that last “Equality” example, I wonder if SQL Server is still using the FK constraint as an optimization to eliminate a JOIN, but going the other way.

    It knows that there are either 0 or 1 rows from dbo.Users matching a UserId. If it returns 1 row, then it needs to read from dbo.Badges where it thinks there will be over 7900 rows. But if it returns 0 rows, then it doesn’t need to read from dbo.Badges at all!


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.