This post is a quick public service announcement to serve as a reminder to be careful with joins in SQL, particularly in queries with a large number of INNER and LEFT JOINs.

An INNER JOIN onto a LEFT JOIN will effectively make that LEFT JOIN an INNER JOIN.

Example

The test query below is against the AdventureWorks2022 database running in compatibility level 160 on a SQL Server 2022 instance. The query shows us all the people with the forename of Amy and all of the sales those people have made, if any:

SELECT  p.FirstName,
        p.LastName,
        h.SalesOrderID,
        h.OrderDate
FROM    Person.Person p
        LEFT JOIN Sales.SalesOrderHeader h
            ON p.BusinessEntityID = h.SalesPersonID
WHERE   p.FirstName = 'Amy'
ORDER BY p.LastName;

We can see that some of the Amys have never made a sale, shown as NULLs in the final two columns.

Now what if we want to see all of the products associated with those sales also:

SELECT  p.FirstName,
        p.LastName,
        h.SalesOrderID,
        h.OrderDate,
        d.OrderQty,
        pr.Name
FROM    Person.Person p
        LEFT JOIN Sales.SalesOrderHeader h
            ON p.BusinessEntityID = h.SalesPersonID
        LEFT JOIN Sales.SalesOrderDetail d
            ON h.SalesOrderID = d.SalesOrderID
        JOIN Production.[Product] pr
            ON pr.ProductID = d.ProductID
WHERE   p.FirstName = 'Amy'
ORDER BY p.LastName;

If we execute this and scroll down the results to the end, we can see from the output that all the Amys who had not made any sales have disappeared from our results:

If I remove the INNER JOIN to the Production.Product table:

SELECT  p.FirstName,
        p.LastName,
        h.SalesOrderID,
        h.OrderDate,
        d.OrderQty
FROM    Person.Person p
        LEFT JOIN Sales.SalesOrderHeader h
            ON p.BusinessEntityID = h.SalesPersonID
        LEFT JOIN Sales.SalesOrderDetail d
            ON h.SalesOrderID = d.SalesOrderID
WHERE   p.FirstName = 'Amy'
ORDER BY p.LastName;

We see that all the Amys who haven’t made any sales are back:

The reason for the lack of Amys with the INNER JOIN in place is that when I added the two further joins to my original query, I carelessly (or intentionally for the sake of this example) made an INNER JOIN to two of my LEFT JOINs which effectively renders both of the LEFT JOINs to be INNER JOINs. The reason for this is that NULL cannot be tested for equality and therefore all the NULLs put out as a result of no match on the LEFT JOIN criteria fail the join condition to the INNER JOIN which, being an INNER JOIN, discards non-matching records.

I can, of course, remedy this by using the correct join:

SELECT  p.FirstName,
        p.LastName,
        h.SalesOrderID,
        h.OrderDate,
        d.OrderQty,
        pr.Name
FROM    Person.Person p
        LEFT JOIN Sales.SalesOrderHeader h
            ON p.BusinessEntityID = h.SalesPersonID
        LEFT JOIN Sales.SalesOrderDetail d
            ON h.SalesOrderID = d.SalesOrderID
        LEFT JOIN Production.[Product] pr
            ON pr.ProductID = d.ProductID
WHERE   p.FirstName = 'Amy'
ORDER BY p.LastName;

Now the sales-shy Amys are back:

Is this SQL bread and butter? Probably. Is it easy to do this by accident? Possibly, particularly if the query is JOINing a large number of tables across a large number of join criteria. Maybe someone else wrote the query originally and you are just editing it and not 100% familiar with the original author’s intention.

So whilst this is probably something we know, it is something that can be easily caused with a rogue INNER JOIN which would affect our results in a way we did not intend. It may also affect the query’s readability further as it may not be obvious to the reader why the LEFT JOINs are LEFT JOINs if an INNER JOIN is then making them redundant. It’s something I always keep in mind when reviewing code or altering queries which hit a large number of tables.