A sql question - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

A sql question

A sql question# Database - 数据库

s*r2006-11-08 08:11

1 楼

I tried two following queries,
Select a.* from a
where a.id in (select id from b);
select a.* from a, b
where a.id=b.id
I assumed two queries shuold have same result. But actually the second query
have more results. Any idea?
Thanks,

I*e2006-11-08 08:11

2 楼

You have NULL values in id column. The join syntax(2nd one) will remove all
NULL comparisons.

query

【在 s******r 的大作中提到】

: I tried two following queries,
: Select a.* from a
: where a.id in (select id from b);
: select a.* from a, b
: where a.id=b.id
: I assumed two queries shuold have same result. But actually the second query
: have more results. Any idea?
: Thanks,

s*r2006-11-08 08:11

3 楼

I checked my tables. There should be no Null values. Actually 2nd talbe has
more records than 1st one.

all

【在 I******e 的大作中提到】

: You have NULL values in id column. The join syntax(2nd one) will remove all
: NULL comparisons.
:
: query

c*e2006-11-08 08:11

4 楼

id不是唯一的

query

【在 s******r 的大作中提到】

I*e2006-11-08 08:11

5 楼

You are selecting from two tables in the statement. Of course, it will have
more rows:
select a.* from a, b
where a.id=b.id
If you want do the same thing as the first one:
Select a.* from a
where exists (select id from b where a.id = b.id);
(this is based on the assumption that you do not have NULL values)

c*e2006-11-08 08:11

6 楼

exists和in本来就没什么大区别

have

【在 I******e 的大作中提到】

: You are selecting from two tables in the statement. Of course, it will have
: more rows:
: select a.* from a, b
: where a.id=b.id
: If you want do the same thing as the first one:
: Select a.* from a
: where exists (select id from b where a.id = b.id);
: (this is based on the assumption that you do not have NULL values)

c*e2006-11-08 08:11

7 楼

我觉得是这种情况
a.id b.id
111 111
222 111
.
.
.
这么一join,就会有两个a.*
如果你能把a.*里面所有的东西都group的话
应该是一样的结果

【在 c*******e 的大作中提到】

: id不是唯一的
:
: query

s*r2006-11-08 08:11

8 楼

I think you are right. I will keep checking. I got table b from my co-worker
. I thought it should have distinct id. but actuall it is not.
I will remove duplicate id from b. Hopefully it will give me same result
this time.

【在 c*******e 的大作中提到】

: 我觉得是这种情况
: a.id b.id
: 111 111
: 222 111
: .
: .
: .
: 这么一join,就会有两个a.*
: 如果你能把a.*里面所有的东西都group的话
: 应该是一样的结果

c*e2006-11-08 08:11

9 楼

join有时候是得小心,可能的话多搞几个field来join
有时候看着可能是redundant,但是那些table里面到底
数据质量怎么样,很难说,经常会有一些意向不到的问题

worker

【在 s******r 的大作中提到】

: I think you are right. I will keep checking. I got table b from my co-worker
: . I thought it should have distinct id. but actuall it is not.
: I will remove duplicate id from b. Hopefully it will give me same result
: this time.

s*r2006-11-08 08:11

10 楼

Confirmed. You are right.
Must be very careful next time. The quality of data is a problem and it will
always be a problem. :(

【在 c*******e 的大作中提到】

: join有时候是得小心,可能的话多搞几个field来join
: 有时候看着可能是redundant,但是那些table里面到底
: 数据质量怎么样,很难说,经常会有一些意向不到的问题
:
: worker

c*e2006-11-08 08:11

11 楼

我以前碰到过这样的问题,呵呵
我的意见是你如果不需要b里面的field
不要做join

will

【在 s******r 的大作中提到】

: Confirmed. You are right.
: Must be very careful next time. The quality of data is a problem and it will
: always be a problem. :(

I*e2006-11-08 08:11

12 楼

This is my fault: IN and EXISTS are the same. NOT IN and NOT EXISTS are
different based on NULL.

【在 c*******e 的大作中提到】

: exists和in本来就没什么大区别
:
: have