How do I convert a nested select to joins when nested select has a temp table?
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?
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.
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).
精彩评论