l******n
发帖数: 9344 | 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*******e
发帖数: 1622 | 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*****g
发帖数: 1275 | 3 select t1.userid
from t1 left outer join t2 on t1.userid = t2.userid
where t2.userid is null
【在 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.
|
w*******e
发帖数: 1622 | 4 你这不对
【在 t*****g 的大作中提到】
: select t1.userid
: from t1 left outer join t2 on t1.userid = t2.userid
: where t2.userid is null
|
t*****g
发帖数: 1275 | 5 where?
【在 w*******e 的大作中提到】
: 你这不对
|
c*****d
发帖数: 6045 | 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******n
发帖数: 9344 | 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*****d
发帖数: 6045 | 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******n
发帖数: 9344 | 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*****g
发帖数: 1275 | 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******n
发帖数: 9344 | 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*****g
发帖数: 1275 | 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*****d
发帖数: 6045 | 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******n
发帖数: 9344 | 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
|
w****n
发帖数: 8 | 15 good!!
【在 t*****g 的大作中提到】
: my query was
: select t1.userid
: from t1 left outer join t2 on t1.userid = t2.userid
: where t2.userid is null
|
b****l
发帖数: 132 | 16 no need left outer join, just left join. will do the same. |
t*****g
发帖数: 1275 | 17 what is left join? :)
【在 b****l 的大作中提到】
: no need left outer join, just left join. will do the same.
|
x***e
发帖数: 2449 | 18 is this a DB board?
It seems you are the only one who talk in SQL.
【在 t*****g 的大作中提到】
: what is left join? :)
|
t*****g
发帖数: 1275 | 19 高手都不来,就我们半坛醋们在这里晃悠.
【在 x***e 的大作中提到】
: is this a DB board?
: It seems you are the only one who talk in SQL.
|
x***e
发帖数: 2449 | 20 hehe,
至少你的概念比较清楚。
【在 t*****g 的大作中提到】
: 高手都不来,就我们半坛醋们在这里晃悠.
|
