I wanted to duplicate rows in my table so I found that

select column_name
from table_name
cross join (select 1 as n union all select 2)n;

worked, but I didn't understand exactly how it worked. So I played around with it to understand how it works.

• I added/deleted columns but didn't have to make any changes in the cross join or union all part of the code. So I understood that the code repeats all the columns, no matter how many.

• I removed the as n from the code in () and it still worked. I replaced the n in the () to m and it still worked. I could basically write any combination of letters, numbers and underscores as the alias for every select in the () part of the code and then type more random letters, numbers and underscores outside the ) and it still worked.

• I understood that the code repeats the rows the number of times select union all is used.

What I don't understand is

• why does the code work even when the n and m are different and/or same and even when no alias is inputted?
• what is the point of the alias in the () part of the code?

CodePudding user response：

why does the code work even when the n and m are different and/or same and even when no alias is inputted?

Your select statement is not referring to any columns from the derived table aliased as n, so the alias name given to the column of the derived table is inconsequential - it doesn't matter what name you give it.

As soon as your statement becomes select column_name, n... or select * then it will matter, or if it were used as part of a view definition.

what is the point of the alias in the () part of the code?

Basically as above, as soon as you want to return the column in the resultset it then (obviously) matters; It's legitimate to have no defined column alias in this situation as it's not required for the derived table's cross join to function as intended, however if this were part of a view or CTE it would matter as columns are required to have an alias.

The derived table above (that's cross-joined) has its own alias, in this example also n however it's not related to or required to match the column alias used as part of the derived table. The SQL syntax requires a derived table - ie any SQL statement enclosed in parenthesis - to have an alias.