Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

SubQuery Expand / Collapse
Author
Message
Posted Tuesday, September 20, 2011 5:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:04 AM
Points: 1,254, Visits: 13,552
good question!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1177783
Posted Tuesday, September 20, 2011 7:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 3,266, Visits: 1,959
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?
Post #1177889
Posted Tuesday, September 20, 2011 9:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 21,346, Visits: 15,022
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1177991
Posted Wednesday, September 21, 2011 4:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
Thanks for Valuable Question.Keep Posting

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1178530
Posted Wednesday, September 21, 2011 6:12 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
Well, I got it wrong [sic] for the right [sic] 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
Post #1178570
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse