Sep 15 2009

No Join Predicate

You could be looking at an execution plan on a query and see this message: Warning, No Join Predicate. With a very few exceptions (very few), that’s not a warning, like “Mind the gap” or “Your Mileage May Vary” or “Never fight a land war in Asia.” No. It’s a screaming shout saying “BY ALL THE GODS IN VALHALA, YOU DON’T HAVE ANY JOIN PREDICATES IN THIS QUERY!” Seriously, that’s exactly what it says.

But, you might be asking yourself, that’s what it says, but what does it mean? Please allow me to explain. The tables in your system, whether it’s a classic BI star schema, or a highly normalized OLTP system, or even (shudder) ORM objects, are related to one another. Usually they’re related through the use of primary and foreign keys. The primary key uniquely identifies a row, like a particular value in a dimension table. This then is related to some number of rows in another table, like in a fact table, through the foreign key. The foreign key acts to protect the integrity of the data, ensuring that if you insert something that’s supposed to relate to another row, then that other row really exists and if you try to delete that other row, the foreign key will remind you that, hey, that row has things that depend on it existing in another table. All of this can be quite complicated or quite simple. Heck, you may be using an ORM system and don’t have a single foreign key in site.

But, when you go to retrieve data from two tables that are related in a TSQL query, you need to define that relationship in the TSQL itself. SQL Server 2008 relies on what was defined as the ANSI standard back in 1992. But, if you learned your SQL coding skills back in the day or on Oracle, you may be used to simply listing tables and then defining their relationships through the WHERE clause. Just a side note, unless you learned your SQL skills in Oracle, if you were born AFTER the 1992 standard was enacted, you don’t have an excuse. Anyway, the old way that code looked like was this:

SELECT…
FROM TableA AS a, TableB as b, TableC as c
WHERE a.MyID = b.MyID
AND b.YourID *= c.YourID
AND….

What you have there is an inner join, or in the old parlance, an equi-join because of the use of the equals sign, between TableA and TableB and an left outer join, defined through the use of the ‘*’, between TableB and TableC. Any other criteria that defined the WHERE clause then followed.

Flat out, in SQL Server 2008, you can’t do this any more. It will not support that style of outer join. The new fangled method, remember, new as of 1992, is this:

SELECT …
FROM TableA as a
INNER JOIN TableB as b
ON a.MyID = b.MyID
LEFT JOIN TableC as c
ON b.YourID = c.YourID
WHERE….

This forces you to separate the relations between the tables as defined by the JOIN’s and the filtering methods supplied through the WHERE clause. And yes, it’s a lot more complicated than I’m making it out to be, but I’m trying to establish the ground rules here. Where was I? Oh yeah, missing join criteria.

What happens is, someone that’s used to writing things in the ANSI ’89 syntax can either forget to define the JOIN criteria or accidently remove it while editing or something. Regardless of how it happened, you’ve ended up with what is known as a cartesian product, shortest possible explanation, everything joined on everything else. For example, here’s a real query against the AdvenureWorks2008 database:

SELECT *
FROM Sales.SalesOrderHeader AS soh
,Sales.SalesOrderDetail AS sod
,Production.Product AS p
WHERE soh.SalesOrderID = 43659

That query does not define join criteria between any of the tables involved. This results in the following estimated execution plan (estimated because I got sick of waiting for the query to finish):

MissingJoin

If you look at the final Nested Loop operation right before the SELECT statement operator, you’ll notice there is a little exclamation point inside a yellow triangle. This is an indication of a warning in your query.  You can hover over the operator to get the tool tip, which looks like this:

NoJoinPredicate

You can see at the bottom the warning that no join predicate is in use. If you constructed the query appropriately it would look more like this:

SELECT *
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductId = p.ProductID
WHERE soh.SalesOrderID = 43659;

Which, instead of returning 800,000 rows before I interrupted it returned only 12 rows and had a very clean execution plan:

Join

I would never consider “No Join Predicate” as a warning. It’s a blaring klaxon of a problem and you need to do something about it, right away.

22 Comments

  • By Mark, September 15, 2009 @ 10:14 am

    Basically it means you get a cartesian result?

  • By scarydba, September 15, 2009 @ 12:54 pm

    Pretty much, yes.

  • By Gail, September 16, 2009 @ 10:46 am

    I have seen this warning before in a query using new-style inner and outer joins where there were join predicates for each join.

    What happened is that one of the join predicates was of the form (Table1.SomeColumn = SomeValue AND Table1.KeyCol1 = Table2.KeyCol1) OR (Table1.SomeColumn = SomeOtherValue AND Table1.KeyCol2 = Table3.KeyCol2)

    That, combined with the other joins ensured there was no cartesian product anywhere in the final query (I checked, carefully), but the optimiser thought there was

  • By scarydba, September 16, 2009 @ 12:02 pm

    Interesting join. I haven’t seen one quite like that. At least so far, when I’ve seen this it’s because of good ole’ fashioned bad code. Good to know that it can come from other sources. Thanks.

  • By Gail, September 16, 2009 @ 2:59 pm

    Well, root cause of this was appallingly bad database design. The ‘one lookup table’ design, iirc, or a variant of it at least

  • By Mark, September 16, 2009 @ 3:28 pm

    Ahh a MUCK table. Multi-Use Code Key…

  • By scarydba, September 16, 2009 @ 6:30 pm

    Oh, I just love those things… I’m migrating an app from Oracle to 2008 right now that has the grand mother of all MUCK tables. It’s a nightmare.

  • By Mark, September 16, 2009 @ 7:01 pm

    Hate ‘em. Pain to use RI with them, make a foreign key. Bahh.

  • By Gail, September 21, 2009 @ 2:39 pm

    Belated apologies, my first comment was not intended as a correction, just as an observation on an interesting exceptional situation that I ran into some years back.

  • By scarydba, September 21, 2009 @ 8:14 pm

    Oh, you saw my comment on the Thread. I was trying to be funny, and failing again. I need to learn to not do that.

  • By Dave, March 18, 2010 @ 2:03 pm

    A bit late to the party, but another example of this in a ‘properly’ joined query was a where clause that was filtering on a column holding a ‘bitwise’ enum type value:

    WHERE (dbo.Companies.Types & @CompanyTypes) 0

    I’d missed that the Companies table was LEFT JOINed; it was the fact that the column could be null that was killing it. Simply changing it to:

    WHERE (ISNULL(dbo.Companies.Types, 0) & @CompanyTypes) 0

    fixed the problem and removed the ‘no join predicate’ warning.

  • By Alan, March 24, 2010 @ 5:35 am

    That’s unless what You want IS a cartesian product.
    I’ve had this with a query that splits rows like ID,number1,number2,number3 to single rows using a additional table with just numbers 1,2,3.
    The query was like
    select id, case when i=1 then number1 when i=2…. end
    from table
    left join table123 on i=i.
    SQL then had to do a full table scan on table123, but that was exactly what I wanted – the table was spooled anyway and it was like 8 byte long.. It’s index was bigger than data.

  • By Jim, August 5, 2010 @ 5:48 am

    Good article. Thanks.

  • By Richard, June 27, 2011 @ 1:06 pm

    Okay, old article – but just a warning that the “very few exceptions” disclaimer is not strictly accurate at all. “No Join Predicate” warnings can be safely ignored if the optimiser decides it would be more efficient to “reverse the order of joins” in your query to put outer joins before inner joins. In this case there will be no join predicate but it doesn’t imply a problem or even a cartesian product.

    Check the cost in the query plan and if it is tiny figures then you are okay to ignore this warning.

  • By Rajeev, June 28, 2011 @ 11:17 am

    I see this warning when I do UNPIVOT with 0% cost. I am not sure though if that’s a situation where it can be ignored too. But haven’t seen any performance issue so far (the dB is not that big so far).

  • By Grant Fritchey, August 5, 2011 @ 11:27 am

    But that does assume that the cost estimates have anything to do with reality.

    However, good point.

  • By Jesse Gibson, January 30, 2012 @ 3:16 pm

    There are false positives, the following gave me MJP’s
    SELECT
    ‘abcd’ as test
    FROM RR_DATA rr
    left join RR_SDATA rrs
    ON rr.RR_NO = rrs.RR_NO
    WHERE rr.RR_NO = 465705

    After Rebuilding the indexes for each and Updating statistics the MJP vanished. Probably the Rebuild did it and the indexes were corrupted at some time in the past.

  • By G Bryant McClellan, June 5, 2012 @ 3:36 pm

    I’m seeing the same thing as Jesse, but no joy after the rebuild and statistics updates. I know it is a false positive. Where else do I look?

  • By Grant Fritchey, June 5, 2012 @ 5:31 pm

    Not sure. Not something I’ve run into. Might want to run a consistency check, just in case.

  • By www.rezi3two-sg.com, February 17, 2014 @ 5:04 pm

    I tend not to drop a leave a response, however after reading a few of the
    remarks on this page No Join Predicate | Home Of The Scary
    DBA. I do have a couple of questions for you if you tend not to mind.
    Is it just me or does it look like a few of these remarks look like they are written
    by brain dead individuals? :-P And, if you are posting on
    other social sites, I would like to follow you. Could you post
    a list of all of all your shared sites like your linkedin profile,
    Facebook page or twitter feed?

Other Links to this Post

  1. Should I be alarmed by this NO JOIN PREDICATE warning? | Question and Answer — April 7, 2013 @ 1:48 pm

  2. SQL Server 2008 query plan warns "No Join Predicate", query takes very long time | BlogoSfera — February 8, 2014 @ 9:03 pm

RSS feed for comments on this post. TrackBack URI

Leave a comment