MySQL to Microsoft SQL Server derived table syntax

I am trying to select data from a derived table created in the FROM statement. I have the following code in MySQL:

SELECT  TEMP.State, TEMP.Name

FROM    (SELECT DISTINCT STATE.State, PRODUCT.Name, PRODUCT.Price
        FROM STATE
        JOIN PRODUCT) AS TEMP

I’m trying to do the same in Microsoft SQL Server, but this doesn’t work, with an error ‘incorrect syntax’. I’ve tried a few different combinations, and anything I can find says this should work. All help appreciated!

Answer

Your current MySQL subquery appears to be a cross join, as it is an inner join without any ON clause. SQL Server does not support this syntax, and if you really want to use a cross join here, then use:

SELECT
    TEMP.State,
    TEMP.Name
FROM
(
    SELECT DISTINCT STATE.State, PRODUCT.Name, PRODUCT.Price
    FROM STATE
    CROSS JOIN PRODUCT
) AS TEMP;

This being said, I see no reason for the subquery on either database, just use:

SELECT DISTINCT s.State, p.Name
FROM STATE s
CROSS JOIN PRODUCT p;