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:

FROM TableA AS a, TableB as b, TableC as c
AND b.YourID *= c.YourID

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:

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

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:

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):


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:


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:

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:


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.

26 thoughts on “No Join Predicate

  1. 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

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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).

  9. There are false positives, the following gave me MJP’s
    ‘abcd’ as test
    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.

  10. 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?

  11. VERY LATE TO THE PARTY, but another cause of this is if you use EXISTS in the WHERE clause, but the outer table is not referenced in the EXISTS (i.e., no correlated sub query).

    e.g., SELECT tableA.* FROM tableA WHERE EXISTS(SELECT * FROM tableB WHERE [email protected])

Leave a Reply