SubQuery
-
good question!
[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life! -
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?
-
Kenneth Wymore (9/20/2011)
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?
That is one place. It is not a very common thing to see - imo. I've seen it in solutions here at SSC. I have also used that method on occasion for that very reason (and because it is faster).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Thanks for Valuable Question.Keep Posting
Malleswarareddy
I.T.Analyst
MCITP(70-451) -
Well, I got it wrong [i]sic[/i] for the right [i]sic[/i] reason: like the rest of you, if my column names don't match in my queries, I usually get errors. Usually. Not here, though, apparently.
Interesting syntax - thanks for the question.
I will now endeavor to forget I ever saw this, or I'll start getting sloppy and be back to column name mismatch errors!
Rich
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply