How does this JOIN work?

Transact-SQL
1

This is from a VIEW that was created by a previous database admin. There are multiple joins, both INNER JOIN and LEFT OUTER JOIN, all of which follow the usual format:

INNER JOIN
mytable
ON
mytable.column1 = something
and mytable.column2 = somethingelse

But then there is this one pair of joins in a structure that I have never before seen and do not understand:

LEFT OUTER JOIN
	dbo.TRACE 
INNER JOIN
	dbo.PHYS_TRACE_COUNT 
ON 
	dbo.TRACE.ID = dbo.PHYS_TRACE_COUNT.TRACE_ID 
ON 
	dbo.PHYS_COUNT_TAG.PHYS_COUNT_ID = dbo.PHYS_TRACE_COUNT.PHYS_COUNT_ID 
	AND dbo.PHYS_COUNT_TAG.TAG_NO = dbo.PHYS_TRACE_COUNT.TAG_NO

What's going on here?

2 
hi 

hope this helps

LEFT OUTER JOIN
    dbo.TRACE 
INNER JOIN
    dbo.PHYS_TRACE_COUNT 
ON 
    dbo.TRACE.ID = dbo.PHYS_TRACE_COUNT.TRACE_ID 
ON 
    dbo.PHYS_COUNT_TAG.PHYS_COUNT_ID = dbo.PHYS_TRACE_COUNT.PHYS_COUNT_ID 
    AND dbo.PHYS_COUNT_TAG.TAG_NO = dbo.PHYS_TRACE_COUNT.TAG_NO
1 Like
3

What you're seeing is a "nested join".

You could think of it like this:

SELECT *
FROM 
    TableA
LEFT OUTER JOIN
    (       TableB
        INNER JOIN
            TableC
        ON 
            TableB.pkey = TableC.fkey
    )
ON 
    TableA.pkey = TableB.fkey

When you remove the (), it looks like your query.

People will sometimes write a join this way to avoid using a RIGHT OUTER JOIN.

SELECT *
FROM 
    TableB
INNER JOIN
    TableC
ON 
    TableB.pkey = TableC.fkey
RIGHT OUTER JOIN
    TableA
ON 
    TableA.pkey = TableB.fkey
3 Likes
4

Thanks!

Somehow I must have missed the "Nested Joins" class. :slight_smile:

5

It's the most skippable day in SQL school.