SQLRNNR (9/19/2011)
Kenneth Wymore (9/19/2011)
Any idea as to why they would allow this behavior in a join? Seems to me like it would actually introduce more confusion than convenience.I think they have to allow it due to the requirement that a subquery that is based on values instead of a query requires the same syntax.
Here's an article on that. http://jasonbrimhall.info/2011/08/31/bitwise-and-derived-table-revisited/
I reviewed your other post about this and I see what you mean about the subquery using values instead of a table select. I have never seen values used in that way exactly but I am sure there are times when it is necessary. When there is a list of static values to reference, I have usually seen it coded as follows.
SELECT *
FROM (
SELECT 1 as a, 2 as b
UNION ALL
SELECT 3 as a, 4 as b
UNION ALL
SELECT 5 as a, 6 as b
UNION ALL
SELECT 7 as a, 8 as b
UNION ALL
SELECT 9 as a, 10 as b
) as MyTable;
--OR using a temp table
IF OBJECT_ID(N'TempDB..#MyTable') IS NOT NULL
BEGIN
DROP TABLE #MyTable
END
CREATE TABLE #MyTable
(a INT, b INT)
INSERT INTO #MyTable
SELECT 1 as a, 2 as b
UNION ALL
SELECT 3 as a, 4 as b
UNION ALL
SELECT 5 as a, 6 as b
UNION ALL
SELECT 7 as a, 8 as b
UNION ALL
SELECT 9 as a, 10 as b
;
SELECT * FROM #MyTable;
Using the union all statements is a bit tedious but that is what I have normally seen. If the same set needs to be used differently for multiple queries then it is typically dropped into a temp table or a regular table. I have seen this option used before just to keep the main query from looking overly complicated too.
I am guessing that using a set of values like you showed on your post would be more common when dealing with applications? For example, where you don't want to insert user supplied values into a table but instead are just using them temporarily in the subquery?