aks a simple SQL question - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

aks a simple SQL question

aks a simple SQL question# Database - 数据库

l*n2006-12-15 08:12

1 楼

【以下文字转载自 shopping 讨论区】
发信人: longtian (自由+回到地球), 信区: shopping
标题: aks a simple SQL question
发信站: BBS 未名空间站 (Fri Dec 15 18:22:51 2006), 转信
two data set both have UserID, want to find out the UserID in the first
set but not in the second one.
You can not use select minus or any form of subqueries.

w*e2006-12-15 08:12

2 楼

may I use cursor?

【在 l******n 的大作中提到】

: 【以下文字转载自 shopping 讨论区】
: 发信人: longtian (自由+回到地球), 信区: shopping
: 标题: aks a simple SQL question
: 发信站: BBS 未名空间站 (Fri Dec 15 18:22:51 2006), 转信
: two data set both have UserID, want to find out the UserID in the first
: set but not in the second one.
: You can not use select minus or any form of subqueries.

t*g2006-12-15 08:12

3 楼

select t1.userid
from t1 left outer join t2 on t1.userid = t2.userid
where t2.userid is null

【在 l******n 的大作中提到】

w*e2006-12-15 08:12

4 楼

你这不对

【在 t*****g 的大作中提到】

: select t1.userid
: from t1 left outer join t2 on t1.userid = t2.userid
: where t2.userid is null

t*g2006-12-15 08:12

5 楼

where?

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

: 你这不对

c*d2006-12-15 08:12

6 楼

nod
should be:
select userid_1
from
(select t1.userid as userid_1, t2.userid as userid_2 from t1 left join t2 on
t1.userid=t2.userid)
where userid_2 is null

你这不对

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

: 你这不对

l*n2006-12-15 08:12

7 楼

do not use subqueries

on

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

: nod
: should be:
: select userid_1
: from
: (select t1.userid as userid_1, t2.userid as userid_2 from t1 left join t2 on
: t1.userid=t2.userid)
: where userid_2 is null
:
: 你这不对

c*d2006-12-15 08:12

8 楼

I do not know your exact requirements
but I guess you are required not to use subquery for performance reason
e.g.
select userid from t1 where userid not in (select userid from t2)
the above statement is resource-costing
But my answer is much efficient even if it is kind of subquery

do not use subqueries
on

【在 l******n 的大作中提到】

: do not use subqueries
:
: on

l*n2006-12-15 08:12

9 楼

this is an interview question.
select the user names which are in table 1 but not in table 2. Do not us
e minus or any subquery
I think you answer still has subquery.

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

: I do not know your exact requirements
: but I guess you are required not to use subquery for performance reason
: e.g.
: select userid from t1 where userid not in (select userid from t2)
: the above statement is resource-costing
: But my answer is much efficient even if it is kind of subquery
:
: do not use subqueries
: on

t*g2006-12-15 08:12

10 楼

try it before you conclude. the where clause is applied after left join
happens. no subquery is needed at all.

on

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

: nod
: should be:
: select userid_1
: from
: (select t1.userid as userid_1, t2.userid as userid_2 from t1 left join t2 on
: t1.userid=t2.userid)
: where userid_2 is null
:
: 你这不对

l*n2006-12-15 08:12

11 楼

I think what you mean is right, but the answer you give includes subquer
y(from (select ***))
My answer is:
select table1.Name, table2.** from table1, table 2 where table1.UserId =
table2.UserId

【在 t*****g 的大作中提到】

: try it before you conclude. the where clause is applied after left join
: happens. no subquery is needed at all.
:
: on

t*g2006-12-15 08:12

12 楼

my query was
select t1.userid
from t1 left outer join t2 on t1.userid = t2.userid
where t2.userid is null

【在 l******n 的大作中提到】

: I think what you mean is right, but the answer you give includes subquer
: y(from (select ***))
: My answer is:
: select table1.Name, table2.** from table1, table 2 where table1.UserId =
: table2.UserId

c*d2006-12-15 08:12

13 楼

oops, you are right
I try the code and no subquery is needed

【在 t*****g 的大作中提到】

: try it before you conclude. the where clause is applied after left join
: happens. no subquery is needed at all.
:
: on

l*n2006-12-15 08:12

14 楼

good,thank you very much

【在 t*****g 的大作中提到】

: my query was
: select t1.userid
: from t1 left outer join t2 on t1.userid = t2.userid
: where t2.userid is null