What is this SQL query doing?-CodePudding

I wish there was a more general language question here, but the whole query feels specifically confusing. :P I've never seen one SQL statement followed by a comma (rather than semicolon), followed by what looks like not another statement, nor what looks like a subquery with additional WHERE clauses after it. So...wtf?

When I run this query, I get a single result set containing two columns: Address and Zip.

This is running against SQL Server btw.

    SELECT 
        [Address] AS [Address],
        [AgencySitesV3].[Zip] AS [Zip]
    FROM
        [AgencySitesV3] WITH (NOLOCK)
        INNER JOIN [Zip] WITH (NOLOCK) ON [AgencySitesV3].[Zip] = [sZipCode]
        ,(
            SELECT gZipPoint
            FROM [Zip] WITH (NOLOCK)
            WHERE sZipCode = '30301'
        ) AS qryOrigin  
    WHERE 
        NULLIF([ActiveApp], '') IS NOT NULL
        AND NULLIF([ActiveScheduling], '') IS NOT NULL
        AND NULLIF([Adult], '') IS NOT NULL

EDIT: Thanks to all that have provided info/comments. It's clear that this query is using SQL-89 join syntax, which is why I've never seen it before. :P However, the query also uses modern joins, which makes it even weirder. I cut a lot of this out for the sake of brevity, but I'm adding one join back in, because now I'm unsure what to use for the ON condition when I replace the SQL-89 join. See the updated query code.

CodePudding user response：

SELECT 
    [Address] AS [Address]
FROM 
    [AgencySitesV3] WITH (NOLOCK)
    ,(
        SELECT gZipPoint
        FROM [Zip] WITH (NOLOCK)
        WHERE sZipCode = '30301'
        ) AS qryOrigin  -- this is the table alias for 
        -- the derived table from the sub query
WHERE 
    NULLIF([ActiveApp], '') IS NOT NULL
    AND NULLIF([ActiveScheduling], '') IS NOT NULL
    AND NULLIF([Adult], '') IS NOT NULL

This query is using old styled joins to get a cartesan product from the addresss column with the derived table's output. Meaning it is getting all combinations of the two outputs. It should be a CROSS JOIN, but whatever.

Simply put - it is getting all addresses for zip code 30301