RE: Querying Data From One Table Against Another Table For All Results

SSC Guru

Points: 186127

August 14, 2012 at 8:17 am

Lynn Pettis (8/14/2012)
ChrisM@Work (8/14/2012)
Here's an alternative using PARSENAME()
SELECT
bi.Building_Name,
COUNT(di.DeviceNum) NumberOfDevices
FROM #Building_Information bi
INNER JOIN #Device_Information di
ON PARSENAME(bi.Subnet,4) = PARSENAME(di.IP_Address,4)
AND PARSENAME(bi.Subnet,3) = PARSENAME(di.IP_Address,3)
AND PARSENAME(bi.Subnet,2) = PARSENAME(di.IP_Address,2)
GROUP BY Building_Name;
Only issue with using parsename() is that it is slow and will disallow the use of indexes on the joining columns if they exist.

Absolutely. Your alternative, using LIKE with a wildcard to the right of the test string, is SARGable. Thanks for the reminder, Lynn.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden