Syntax error on Union query when using an Alias for subquery-CodePudding

When I add an alias to the second subquery, I receive a syntax error in SQL Server. Why is this the case? Can someone explain? Here's the example of syntax error:

Msg 102, Level 15, State 1, Line 29 Incorrect syntax near 's'.:

SELECT *
FROM

(
    SELECT 'Fail' [Status],fail_date[Date]
    FROM Failed
) f

UNION ALL
(
    SELECT 'Success' [Status],success_date[Date]
    FROM Succeeded
) s

When removing the alias in the second subquery, it executes without any errors. Confused as to why an alias is allowed in the first subquery but not the second.

SELECT *
FROM

(
    SELECT 'Fail' [Status],fail_date[Date]
    FROM Failed
) f

UNION ALL
(
    SELECT 'Success' [Status], success_date[Date]
    FROM Succeeded
)

CodePudding user response：

You see it better in my last query

you union

SELECT *
FROM

(
    SELECT 'Fail' [Status],fail_date[Date]
    FROM Failed
) f

with

(
    SELECT 'Success' [Status],success_date[Date]
    FROM Succeeded
)

UNION doesn't need an alias, as you can see in the first union query

CREATE TABLe Succeeded (success_date date)
GO

CREATE TABLe Failed (fail_date date)
GO

SELECT *
FROM

(
    SELECT 'Fail' [Status],fail_date[Date]
    FROM Failed


UNION ALL

    SELECT 'Success' [Status],success_date[Date]
    FROM Succeeded
) s
GO

Status | Date
:----- | :---

SELECT *
FROM

(
    SELECT 'Fail' [Status],fail_date[Date]
    FROM Failed
) f

UNION ALL
(
    SELECT 'Success' [Status],success_date[Date]
    FROM Succeeded
)

GO

Status | Date
:----- | :---

SELECT *
FROM

(
    SELECT 'Fail' [Status],fail_date[Date]
    FROM Failed
) f

UNION ALL

    SELECT 'Success' [Status],success_date[Date]
    FROM Succeeded
 
GO

Status | Date
:----- | :---

db<>fiddle here

CodePudding user response：

The TL;DR answer is that table and subquery aliases have to exist inside a from clause.

I think it's actually your use of parentheses which is confusing you, because what you've done is actually try to alias a whole "select". The fact that the query happens to involve a union is not really relevant.

In the failing query, you have this kind of structure after the union:

(select * from MyTable) t; -- invalid, can't have an alias for a whole select.

Notice how the alias t here is outside the parentheses which surround the entire select statement. SQL is looking for a from clause:

... from (select * from MyTable) t; -- ok, the alias "t" will be used in the outer "from"

If the alias was inside the parentheses, it would be fine:

(select * from MyTable t); -- ok, alias is part of the from clause.

and of course it should be clear that this query doesn't actually need the parentheses:

select * from MyTable t; -- exactly the same as the previous query

So, in the case of your failing structure, you have:

select * from (some query) t -- alias is fine, it's used by the outer from clause
union all
(select * from whatever) u -- not ok, can't alias a whole select.

If you just remove the second set of parentheses, it's fine:

select * from (some query) t
union all
select * from whatever u;

Another way of understanding this is to understand that the "from" clause in the first part of the query "ends" at the union. The select after the union is an entirely new select statement with its own from clause:

select * from MyTable t1 -- this from clause is now done

union all -- we are starting a whole new select, it's not part of the above "from"

select * from MyTable t2