In this answer it is shown how a literal table can be created. However, I want to perform a full outer join or inner join on two literal tables, but I cannot figure out the syntax of the literal table after the join.
I have tried several variations on this:
SELECT * FROM
(VALUES (1), (3), (5), (7), (9) ) AS X(a)
JOIN
(VALUES (0), (2), (4), (6), (8) ) AS Y(b);
I get the error:
SQL Error (102): Incorrect syntax near ')'.
I have tried removing the outer parentheses around the second literal table:
SQL Error (156): Incorrect syntax near the keyword 'VALUES'.
I tried with and without the AS statements, and also tried removing other parentheses wherever I could think of.
What is the correct syntax to join two literal tables in T-SQL?
>Solution :
Simply add the ON clause, as you figured it out yourself:
SELECT * FROM
(VALUES (1), (3), (5), (7), (9) ) AS X(a)
full outer JOIN
(VALUES (0), (2), (4), (6), (8) ) AS Y(b)
on X.a=Y.b;