May 3, 2012 at 4:19 am
Hi All
I can across something today that I haven't encountered before (due to a typo in my syntax actually) and am interested in finding the answer to, consider the following:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET DATEFORMAT DMY
DECLARE @TABLE1 AS TABLE
(VALUE INT)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
DECLARE @TABLE2 AS TABLE
(VALUE2 INT)
INSERT INTO @TABLE2
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
DECLARE @TABLE3 AS TABLE
(TEST INT)
INSERT INTO @TABLE3
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
SELECT *
FROM
@TABLE1 AS T1
INNER JOIN @TABLE2 AS T2
ON T1.VALUE = T2.VALUE2
WHERE
T1.VALUE IN (SELECT VALUE FROM @TABLE3)
How come this syntax runs, the column VALUE does not exist in TABLE3 and therefore I am not SELECTing anything from TABLE3?
I of course know that subqueries can reference the outer query so it is essentially doing the following:
WHERE
T1.VALUE IN (SELECT T1.VALUE)
I would have thought in the IN statement SELECT I would have to at least reference a valid column from TABLE3 or not SELECT a column at all i.e. change VALUE in the IN clause to a hard coded 1 or something which would be valid as the syntax would be SELECT 1 FROM Table3 which is valid? :crazy:
As I said this is purely out of interest as I've never seen this before?
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 3, 2012 at 4:50 am
Andy Hyslop (5/3/2012)
How come this syntax runs, the column VALUE does not exist in TABLE3 and therefore I am not SELECTing anything from TABLE3?
Because there's nothing in SQL that requires you to actually select data from a table referenced in the query
I of course know that subqueries can reference the outer query so it is essentially doing the following:
WHERE
T1.VALUE IN (SELECT T1.VALUE)
Exactly what it's doing
I would have thought in the IN statement SELECT I would have to at least reference a valid column from TABLE3 or not SELECT a column at all i.e. change VALUE in the IN clause to a hard coded 1 or something which would be valid as the syntax would be SELECT 1 FROM Table3 which is valid? :crazy:
No, all that's required is that any column is bindable to a table in scope, which it is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2012 at 5:02 am
When I looked first it did looked strange, but in few seconds...
see:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET DATEFORMAT DMY
DECLARE @TABLE1 AS TABLE
(VALUE INT)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
DECLARE @TABLE3 AS TABLE
(TEST INT)
INSERT INTO @TABLE3
SELECT 0
SELECT *
FROM @TABLE1 AS T1
WHERE T1.VALUE = (SELECT VALUE FROM @TABLE3)
and
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET DATEFORMAT DMY
DECLARE @TABLE1 AS TABLE
(VALUE1 INT)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
DECLARE @TABLE3 AS TABLE
(TEST INT)
INSERT INTO @TABLE3
SELECT 0
SELECT *
FROM @TABLE1 AS T1
WHERE T1.VALUE1 = (SELECT VALUE1 FROM @TABLE3)
Can you see now which table "VALUE" column is selected from in the "IN"?
May 3, 2012 at 5:44 am
This is why you should always use qualify your column names.
This would have thrown an error.
SELECT *
FROM @TABLE1 AS T1
INNER JOIN @TABLE2 AS T2 ON T1.VALUE = T2.VALUE2
WHERE T1.VALUE IN (SELECT T3.VALUE FROM @TABLE3 T3)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2012 at 5:59 am
GilaMonster (5/3/2012)
This is why you should always use qualify your column names.This would have thrown an error.
SELECT *FROM @TABLE1 AS T1
INNER JOIN @TABLE2 AS T2 ON T1.VALUE = T2.VALUE2
WHERE T1.VALUE IN (SELECT T3.VALUE FROM @TABLE3 T3)
I also use the simple rule:
Query with one table involved - don't need alias,
more than one table: alias for every table.
It helps to prevent such "strange" behaviour...
May 3, 2012 at 6:38 am
Thanks both
Eugene yep I knew what it was doing it was more a question of is this expected behaviour.
OK dumb question then if all that is needed is to bind a table to a scope how come the following fails:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET DATEFORMAT DMY
DECLARE @TABLE1 AS TABLE
(VALUE INT)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
DECLARE @TABLE3 AS TABLE
(TEST INT)
INSERT INTO @TABLE3
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
SELECT *
FROM
@TABLE1 AS T1
INNER JOIN(SELECT
VALUE
FROM
@TABLE3
) AS T3
ON T1.VALUE = T3.VALUE
Now don't get me wrong I know it will fail as VALUE does not exist in TABLE3 however how come this syntax errors but place the same syntax in a WHERE and it will execute?
I may be answering my own question here but is it because SQL evaluates inner subquery's before the outer query and as this column does not exist = Error..?
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 3, 2012 at 7:04 am
Subqueries in the select and where are allowed to be correlated (to reference columns in the outer query). Subqueries in the from are not allowed to be correlated unless they are used with cross apply
Your code fails, not because of order of evaluation, but simply because a subquery used with inner join is not allowed to be correlated.
This works
DECLARE @TABLE1 AS TABLE
(VALUE INT)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
DECLARE @TABLE3 AS TABLE
(TEST INT)
INSERT INTO @TABLE3
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
SELECT *
FROM
@TABLE1 AS T1
CROSS APPLY(SELECT
T1.VALUE
FROM
@TABLE3
) AS T3
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2012 at 7:16 am
Gail thank-you, explained so well even I can understand it 😉
I've been using T-SQL/ P-SQL for years and am by no means a beginner, although compared to some of you guys on this site who's knowledge of SQL is astounding I feel like it sometimes! :hehe:
I'm completely self taught so sometimes I come across things like this and just have to know.....Why?!!!
Only way to learn eh? 😉
(well except for certification which I'm looking at next)
Thanks again
Andy
Slight Edit: How come its allowed with a CROSS APPLY?
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 3, 2012 at 7:28 am
Because the whole point of cross apply it to allow the passing of a column from another table to a function or subquery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2012 at 7:40 am
GilaMonster (5/3/2012)
Because the whole point of cross apply it to allow the passing of a column from another table to a function or subquery.
I've used CROSS APPLY many times in the past and knew its function my question was more of the internal process as to the how is it allowed.
Thank-you again Gail, I've got some reading to do I think! I'll take it from here 😀
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 3, 2012 at 7:51 am
Nothing to say on the how, it's the same as any other correlated subquery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply