SQLRNNR (9/19/2011)

---

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/

Kenneth Wymore (9/20/2011)

---

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?