The Subquery

Question

Post reply

The Subquery

malleswarareddy_m

SSCertifiable

Points: 5847
More actions
May 8, 2013 at 9:46 pm

#277508

Comments posted to this topic are about the item The Subquery
Malleswarareddy
I.T.Analyst
MCITP(70-451)
May 9, 2013 at 2:50 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Nice question....

Here are some more scenarios:

--Scenario 1

create table address_staging

(client int primary key,addressdetails varchar(250));

insert into address_staging

select 100,'hyderbad,india'

union all

select 101,'banglore,india'

union all

select 102,'banglore,india'

;

create table address_oltp

(client_id int primary key,address_details varchar(250));

insert into address_oltp

select 104,'newyork,usa'

union all

select 105,'chicago,usa'

union all

select 106,'washington,usa'

;

select *

from address_oltp

where client_id in (select client_id from address_staging)

--result

client_idaddress_details

104newyork,usa

105chicago,usa

106washington,usa

---Scenario 2

select *

from address_oltp

where client_id in (select client from address_staging)

--Result

It will return 0 rows

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 2

unfortunately, i trapped in this question. :crying:

But i learn something new. 🙂

Thanks reddy

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 3

Nice question...

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Toreador SSChampion Points: 11365 More actions · Answer 4

A good question, but I'm not so sure about the explanation.

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error.

Fair enough, but this isn;'t the case here, as the ciolumn referenced in the subquery does exist in the table referenced by the subquery's FROM clause.

nenad-zivkovic Default port Points: 1448 More actions · Answer 5

Toreador (5/9/2013)
A good question, but I'm not so sure about the explanation.
If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error.
Fair enough, but this isn;'t the case here, as the ciolumn referenced in the subquery does exist in the table referenced by the subquery's FROM clause.

No it does not. You should look carefully - table in subquery (address_staging) have a column clientid and select is using client_id. Mind the underscore _.

Funny fact - I've recently wrote something (article) about this - I've called it "Accidental correlated subqueries". (So I've spotted it on sight here 🙂 ) This is actually quite possible to happen in real life situations, and could be very dangerous when used with delete statement. We've once ruined a production table because of it (true story).

Best practice to make sure you don't make mistake with incorrect column names should be to always use table names or aliases in front of column names:

select *

from address_oltp t1

where t1.client_id in (select t2.client_id from address_staging t2)

Have the code been written like this it would produce an error and you would spot something is not written correctly.

_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

Toreador SSChampion Points: 11365 More actions · Answer 6

Well spotted 🙂

I'd say best practice is not only to use aliases, but also to adopt and stick to some standard naming conventions for your database!

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 7

I faced this issue when coding for my project work. We follow naming conventions and we use '_' for oltp tables. Before doing the functional testing I usually test each line of code in my procedure.

While testing the below line of code I found that it was not throwing errors and was fetching records (although incorrectly) even though the column name (client_id) in the subquery did not exist.

select *

from address_oltp

where client_id in (select client_id from address_staging)

So I verified and fixed this issue with below code.

select *

from address_oltp

where client_id in (select clientid from address_staging)

I found the reason why the first code did not show any errors in the msdn website.

The reference link is given below.

http://msdn.microsoft.com/en-IN/library/ms178050(v=sql.105).aspx

Microsoft should provide information message when developer is doing mistakes. For ex., If dependent procedure is missing when creating procedure it will show “The module '%.*ls' depends on the missing object '%.*ls'. The module will still be created; however, it cannot run successfully until the object exists.”

Malleswarareddy
I.T.Analyst
MCITP(70-451)

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 8

nenad-zivkovic (5/9/2013)
Toreador (5/9/2013)
A good question, but I'm not so sure about the explanation.
If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error.
Fair enough, but this isn;'t the case here, as the ciolumn referenced in the subquery does exist in the table referenced by the subquery's FROM clause.
No it does not. You should look carefully - table in subquery (address_staging) have a column clientid and select is using client_id. Mind the underscore _.
Funny fact - I've recently wrote something (article) about this - I've called it "Accidental correlated subqueries". (So I've spotted it on sight here 🙂 ) This is actually quite possible to happen in real life situations, and could be very dangerous when used with delete statement. We've once ruined a production table because of it (true story).
Best practice to make sure you don't make mistake with incorrect column names should be to always use table names or aliases in front of column names:
select *
from address_oltp t1
where t1.client_id in (select t2.client_id from address_staging t2)
Have the code been written like this it would produce an error and you would spot something is not written correctly.

It's true. am also using alias when writing code. this issue i have faced almost one and half year back. but i posted this question few months back.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

manik_anu SSCrazy Points: 2377 More actions · Answer 9

Danny Ocean (5/8/2013)
unfortunately, i trapped in this question. :crying:
But i learn something new. 🙂
Thanks reddy

me too.. But it will be helped in my future...

thanks nice question....

Manik
You cannot get to the top by sitting on your bottom.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

Client_id vs Clientid - I am glad I noticed this in the last minute.

The question pretends to be about subqueries, but I am pretty sure that the 38% people that picked the "no rows" option were caught off guard by this. Suggestion to the author of the question - next time, if you want to demonstrate something, make it stand out instead of trying to hide it. I get that in a real system, this kind of error can happen with subtle spelling differences between columns names (if no naming standards are used). But in a question that focuses on educating about subquery scope, you should make it stand out so that the readers know what to focus on.

That being said - I appreciate the effort of submitting a QotD, and I hope to see more of you in the future.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

paul.knibbs SSCoach Points: 15320 More actions · Answer 11

Have to admit, I'm puzzled by this behaviour. It doesn't make logical sense for SQL to make a substitution like this when the table you're selecting from is explicitly named in the subquery, surely? :ermm:

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 12

Good one, thank you for the post.

(so basic, and yet so important in our daily script writings and need to keep an eye on it.)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

nenad-zivkovic Default port Points: 1448 More actions · Answer 13

paul.knibbs (5/9/2013)
Have to admit, I'm puzzled by this behaviour. It doesn't make logical sense for SQL to make a substitution like this when the table you're selecting from is explicitly named in the subquery, surely? :ermm:

It's not really making any substitution. Yes, there is a table mentioned in subquery in FROM but the columns in SELECT can also come from outer query. It is perfectly OK to use columns from outer table anywhere in subquery - and SQL Server is not gonna make a guessing whatever you planned from outer or inner table. If it exist in one and not another it's going to be used.

Since nothing from subquery's table is actually selected here - it can very well be omitted. Any of these would be exactly the same:

select * from address_oltp where client_id in (select client_id from address_staging)

select * from address_oltp where client_id in (select client_id)

select * from address_oltp where client_id = client_id

select * from address_oltp where 1=1

select * from address_oltp

_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)