How do I convert a nested select to joins when nested select has a temp table?

Question

I have the following SQL. Tables defined.

#tbContinent 
--------
ContinentID | ContinentName 

AddressInCountry
--------
AddressID | CountryID

AddressInContinent
--------
AddressID | ContinentID

NameInAddress
--------
AddressID | NameID

I then have the following SQL:

---     Insert into tbName
DECLARE @tbName TABLE 
([ID] int, [AddressID] int, [Name] ntext)

INSERT INTO @tbName 
SELECT 
    [Name].[NameID] AS [ID],
    [Address].[AddressID],
    [Name].[Name]
    FROM NameInAddress INNER JOIN [Name] ON NameInAddress.NameID = Name.NameID
    INNER JOIN [Address] ON NameInAddress.AddressID = Address.AddressID


WHERE [Address].[AddressID] IN
(
    SELECT  AddressInCountry.AddressID
     FROM AddressInCountry 
    UNION ALL
    SELECT  AddressInContinent.AddressID
     FROM #tbContinent AS Continent
     JOIN AddressInContinent ON Continent.ContinentID = AddressInContinent.ContinentID  
)

I've been asked to use joins in place of the nested select. Ive done similar ones using views to create the Union and perform joins on the view, but this one uses a temp table which I can't pass into my view so dont think that I can use the same technique.

I can't use nested selects at all.

Any ideas?

Answer 1

JOIN 
(
    SELECT  AddressInCountry.AddressID
    FROM AddressInCountry 
    UNION --ALL
    SELECT  AddressInContinent.AddressID
    FROM #tbContinent AS Continent
    JOIN AddressInContinent ON Continent.ContinentID = AddressInContinent.ContinentID        
) AS aic
ON aic.AddressID = [Address].[AddressID]

i'm suggesting UNION over UNION ALL here because you don't want duplicate entries (to match EXISTS functionality).

Answer 2

Let's take another shot at an answer.

Looking at Set Theory / Relational Algebra, the operation done by a WHERE EXISTS call is known as a LEFT SEMI JOIN (and NOT EXISTS is a LEFT ANTI SEMI JOIN). This means that the join is only used as a filter, it doesn't pull in extra fields, nor does it cause duplicates in the data in the left table.

To implement a semi join without EXISTS, you can do an inner join, making sure there are no duplicates in the right table, as in my other answer.

As you want to extract out the UNION ALL, you would need to put the other join logic in a query with the first part of the union, and then duplicate that logic in the second.

But then the fact that a semi join doesn't duplicate entries on the left means that you need to watch for duplicates, but still somehow include duplicates that could be there in the original version.

Honestly, the correct approach here is to use a subquery, and WHERE EXISTS is the ideal way to implement a Left Semi Join.