An obvious error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

An obvious error

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-06 : 21:17:26

CREATE TABLE Table1 (
Name VARCHAR (100),
Email VARCHAR(100))

CREATE TABLE Table2 (
Name VARCHAR (100),
E-mail VARCHAR (100))

-- None of the Email values in Table2 match the Email values in Table1, but let's be sure...
-- Find all rows in Table1 that match rows in Table2
SELECT *
FROM Table1
WHERE Email IN (SELECT Email FROM Table2)

-- Returns all rows in Table1. Hmmmmm...

(May be old hat to some, but it's another gotcha that surprised me anyway.)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-06 : 22:01:58

That's one of many good reasons to always put an alias on every table and qualify every column name in a query with an alias.

CODO ERGO SUM

jhermiz

3564 Posts

Posted - 2005-07-06 : 22:53:06

quote:
Originally posted by SamC

CREATE TABLE Table1 (
Name VARCHAR (100),
Email VARCHAR(100))

CREATE TABLE Table2 (
Name VARCHAR (100),
E-mail VARCHAR (100))

-- None of the Email values in Table2 match the Email values in Table1, but let's be sure...
-- Find all rows in Table1 that match rows in Table2
SELECT *
FROM Table1
WHERE Email IN (SELECT Email FROM Table2)

-- Returns all rows in Table1. Hmmmmm...

(May be old hat to some, but it's another gotcha that surprised me anyway.)

Try joining the two tables on TABLE1.Email = TABLE2.Email

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-07 : 00:27:14

quote:
Originally posted by jhermiz

Try joining the two tables on TABLE1.Email = TABLE2.Email

Yes, rewriting the query would fix the problem...

But I was surprised the original query I posted above didn't work. It took me a minute to find the problem.

Once I found the problem, I was a little surprised that the query compiled.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 03:30:38

Sam, I dont think so


declare @t1 table(id int, mailid varchar(100))
insert into @t1 values(1,'test1@test1.com')
insert into @t1 values(2,'test2@test1.com')
insert into @t1 values(3,'test3@test1.com')
declare @t2 table(id int, mailid varchar(100))
insert into @t2 values(1,'test1@test2.com')
insert into @t2 values(2,'test2@test2.com')
insert into @t2 values(3,'test3@test2.com')
select * from @t1 where mailid in (select mailid from @t2)

No rows returned

Madhivanan

Failing to plan is Planning to fail

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-07-07 : 03:38:15

hehehehehehe

From some of the responses, it would seem the error isn't as obvious to everyone as you might think Sam

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-07 : 08:10:21

quote:
Originally posted by madhivanan

Sam, I dont think so

Like many errors, it isn't complicated, just obscure.

Here are Madhivanan's inserts which I've modified to work with the query in the first post...

-- Insert some data

insert into Table1 values(1,'test1@test1.com')
insert into Table1 values(2,'test2@test1.com')
insert into Table1 values(3,'test3@test1.com')

insert into Table2 values(1,'test1@test2.com')
insert into Table2 values(2,'test2@test2.com')
insert into Table2 values(3,'test3@test2.com')

-- and once again
-- None of the Email values in Table2 match the Email values in Table1, but let's be sure...
-- Find all rows in Table1 that match rows in Table2
SELECT *
FROM Table1
WHERE Email IN (SELECT Email FROM Table2)

-- Result set
1 test1@test1.com
2 test2@test1.com
3 test3@test1.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 08:21:35

It seems very strange
I did not get the result(which seems correct)
Any other reason?

Madhivanan

Failing to plan is Planning to fail

jhermiz

3564 Posts

Posted - 2005-07-07 : 08:22:43

quote:

Originally posted by madhivanan

Sam, I dont think so


declare @t1 table(id int, mailid varchar(100))
insert into @t1 values(1,'test1@test1.com')
insert into @t1 values(2,'test2@test1.com')
insert into @t1 values(3,'test3@test1.com')
declare @t2 table(id int, mailid varchar(100))
insert into @t2 values(1,'test1@test2.com')
insert into @t2 values(2,'test2@test2.com')
insert into @t2 values(3,'test3@test2.com')
select * from @t1 where mailid in (select mailid from @t2)

No rows returned

Madhivanan

Failing to plan is Planning to fail

Try the same thing without using temp tables.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-07 : 08:31:22

quote:
Originally posted by madhivanan

It seems very strange
I did not get the result(which seems correct)
Any other reason?

If you run the scripts I've posted, including the create tables, you'll quickly find the problem, (and you'll have to correct one syntatic error -- sorry about that

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 08:34:22

Yes tried with tables
But No result


CREATE TABLE Table1 (
Name VARCHAR (100),
Email VARCHAR(100))

CREATE TABLE Table2 (
Name VARCHAR (100),
Email VARCHAR (100))

insert into Table1 values(1,'test1@test1.com')
insert into Table1 values(2,'test2@test1.com')
insert into Table1 values(3,'test3@test1.com')

insert into Table2 values(1,'test1@test2.com')
insert into Table2 values(2,'test2@test2.com')
insert into Table2 values(3,'test3@test2.com')

SELECT *
FROM Table1
WHERE Email IN (SELECT Email FROM Table2)

drop table Table1
drop table Table2

Madhivanan

Failing to plan is Planning to fail

wiltech
Yak Posting Veteran

51 Posts

Posted - 2005-07-07 : 08:43:23

quote:
Originally posted by SamC

quote:
Originally posted by jhermiz

Try joining the two tables on TABLE1.Email = TABLE2.Email

Yes, rewriting the query would fix the problem...

But I was surprised the original query I posted above didn't work. It took me a minute to find the problem.

Once I found the problem, I was a little surprised that the query compiled.

In your original post you define the email field in Table2 as "E-mail"

Your intended query is:

SELECT *
FROM Table1
WHERE Table1.Email IN (SELECT Table2.Email FROM Table2)

which won't compile

The correct query for the tables, as you defined them, should be:

SELECT *
FROM Table1
WHERE Table1.Email IN (SELECT Table2.[E-mail] FROM Table2)

Tony W

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 08:47:47

quote:
The correct query for the tables, as you defined them, should be:
SELECT *
FROM Table1
WHERE Table1.Email IN (SELECT Table2.E-mail FROM Table2)

Absolutely correct
I wonder why there is no error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Email'.

Madhivanan

Failing to plan is Planning to fail

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-07 : 09:03:16

quote:
Originally posted by madhivanan

quote:
The correct query for the tables, as you defined them, should be:
SELECT *
FROM Table1
WHERE Table1.Email IN (SELECT Table2.E-mail FROM Table2)

Absolutely correct
I wonder why there is no error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Email'.

That's not exactly correct.

There's no error in:
SELECT *
FROM Table1
WHERE Email IN (SELECT Email FROM Table2)

What is odd about the IN clause is why would anyone SELECT Email (which is in Table1)? It bothered me that the compiler allowed this, but when you consider the general case of building a SELECT list...

IN (SELECT Table1.Col1 <operation> Table2.Col1 Table2 WHERE <condition on Table1 or Table2>)

Allowing an <operation> seems reasonable in the result list and should include Table1. It's a little strange that it allows omission of Table2, but

WHERE Email IN (SELECT Email FROM Table2) -- is valid, but it's even shorter to write

WHERE Email IN (SELECT Email) -- Which is probably also valid, but I haven't tested it.

WHERE Email = Email -- Is the shortest

The obscurity of this error (for me) was Table2 had a column [E-mail], not Email. The syntax error I mentioned above was:

CREATE TABLE Table2 (
Name VARCHAR (100),
[E-mail] VARCHAR (100)) -- E-mail must be in brackets

wiltech
Yak Posting Veteran

51 Posts

Posted - 2005-07-07 : 09:14:56

quote:
Originally posted by SamC

That's not exactly correct.

There's no error in:
SELECT *
FROM Table1
WHERE Email IN (SELECT Email FROM Table2)

What is odd about the IN clause is why would anyone SELECT Email (which is in Table1)? It bothered me that the compiler allowed this, but when you consider the general case of building a SELECT list...

IN (SELECT Table1.Col1 <operation> Table2.Col1 Table2 WHERE <condition on Table1 or Table2>)

Allowing an <operation> seems reasonable in the result list and should include Table1. It's a little strange that it allows omission of Table2, but

WHERE Email IN (SELECT Email FROM Table2) -- is valid, but it's even shorter to write

WHERE Email IN (SELECT Email) -- Which is probably also valid, but I haven't tested it.

WHERE Email = Email -- Is the shortest

The obscurity of this error (for me) was Table2 had a column [E-mail], not Email. The syntax error I mentioned above was:

CREATE TABLE Table2 (
Name VARCHAR (100),
[E-mail] VARCHAR (100)) -- E-mail must be in brackets

So there was a syntax error in the CREATE TABLE statement and a logic error in the SELECT statement.

Tony W

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-07-07 : 09:18:18

quote:
WHERE Email IN (SELECT Email FROM Table2)

The result isn't completely independent of Table2: it's equivalent to

WHERE Email = Email AND EXISTS (SELECT * FROM Table2)

which is why you'll see the row count spool and semi-join in the execution plan.
The fact that it's valid is a just consequence of the scoping rules for subqueries, though I agree that a warning would be nice.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-07 : 09:18:46

What you have is a correlated sub-query. You are allowed to use any column from the outer query in a sub-query, so the column name Email is unambiguous (to SQL Server) in the context of your query, because it does not exist in Table2

As I mentioned in my first post, it is a good reason to use an alias for all column names. If you had written your query in the following syntax, you would have received an error, instead of unexpected query results.


select
	a.*
from
	Table1 a
where
	a.Email in 
	( select b.Email from Table2 b )

CODO ERGO SUM

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-07 : 09:19:09

Clear! You can have something like ... in (select table1.<any column>, ... from table2 where <condition involving table1 and table2 columns> ... When original query is resolved inner select it is done like cross join between table1 and table2, with only table1.email column in a select list. Right?

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-07 : 09:57:18

quote:
Originally posted by wiltech

So there was a syntax error in the CREATE TABLE statement and a logic error in the SELECT statement.

Right. In my encounter with this problem, the tables were existing tables, so there was only one error ... the logic error in the SELECT.

The syntax error in the CREATE TABLE wasn't intended, but it did help obscure the problem a little. I hope in a nice way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources