Multiple Joins on Temporary Table OR Subquery in SQL 2008

I have the following SQL which gets a season for each day in a range of dates, then groups each season by start and end date with number of nights. What it does is not important but my question is which is better, the way I've done it below or use the first select statement as a subquery each time @dateSeasons is used in the second query. Both ways seem to run the same but this way looks neater.

DECLARE @dateSeasons TABLE ([date] date, seasonID int)

INSERT INTO @dateSeasons
SELECT D.[date], S.ID
FROM @dates AS D
CROSS APPLY (

    SELECT TOP 1 ID
    FROM dbo.Seasons
    WHERE bookingID = @bookingID 
    AND D.[date] BE开发者_运维技巧TWEEN startDate AND endDate
    ORDER BY ID DESC

) AS S


SELECT MIN([date]), endDate, DATEDIFF(DAY, MIN([date]), DATEADD(DAY, 1, endDate)), seasonID
FROM (

    SELECT S1.seasonID, S1.[date], (

        SELECT MAX([date])
        FROM @dateSeasons S2
        WHERE S2.seasonID = S1.seasonID
        AND NOT EXISTS (

            SELECT NULL
            FROM @dateSeasons S3
            WHERE S3.[date] < S2.[date]
            AND S3.[date] > S1.[date]
            AND S3.seasonID <> S1.seasonID

        )

    ) AS endDate
    FROM @dateSeasons S1
) AS results
GROUP BY endDate, seasonID
ORDER BY MIN([date])

Looking neater is irrelevant in writing SQL Code. What looks elegant is often the worst possible way to solve the problem from a performance standpoint.

The only way to know for sure which is best is to first make sure both ways you are testing return the same results and then performance test them and check out the execution plans (or explain in mySQL). Techniques which make the query better are database specific as well. What works best to performance tune in SQL Server might be the worst possibility in Oracle.

Sometimes you can get better performance by using a common table expression (CTE):

WITH
dateSeasons ([date], [seasonID])
AS
(
    SELECT D.[date], S.ID
    FROM @dates AS D
    CROSS APPLY (

        SELECT TOP 1 ID
        FROM dbo.Seasons
        WHERE bookingID = @bookingID 
        AND D.[date] BETWEEN startDate AND endDate
        ORDER BY ID DESC

    ) AS S
)

SELECT MIN([date]), endDate, DATEDIFF(DAY, MIN([date]), DATEADD(DAY, 1, endDate)), seasonID
FROM (

    SELECT S1.seasonID, S1.[date], (

        SELECT MAX([date])
        FROM dateSeasons S2
        WHERE S2.seasonID = S1.seasonID
        AND NOT EXISTS (

            SELECT NULL
            FROM dateSeasons S3
            WHERE S3.[date] < S2.[date]
            AND S3.[date] > S1.[date]
            AND S3.seasonID <> S1.seasonID

        )

    ) AS endDate
    FROM dateSeasons S1
) AS results
GROUP BY endDate, seasonID
ORDER BY MIN([date])