是否可以限制外连接的结果?

发布于 2024-08-08 03:06:47 字数 497 浏览 5 评论 0原文

我有一个场景,我需要跨三个表进行联接。

table #1 is a list of users
table #2 contains users who have trait A
table #3 contains users who have trait B

如果我想找到所有具有特征 A 或特征 B 的用户(在一个简单的 sql 中),我想我被困住了。

如果我进行常规连接,不具有特征 A 的人将不会出现在结果集中以查看他们是否具有特征 B(反之亦然)。 但是,如果我从表 1 到表 2 和表 3 执行外连接,则无论我的 where 子句的其余部分指定了针对表 2 或 3 的要求,我都会获得表 1 中的所有行。

在提出多个 sql 和 temp 之前表之类的,这个程序要复杂得多,这只是简单的情况。它根据许多外部因素动态创建 sql,因此我试图使其在一个 sql 中工作。 我希望 in 或exists 的组合可以起作用,但我希望有一些简单的东西。 但基本上外连接总是会产生表 1 中的所有结果,是吗?

I've got a scenario where I need to do a join across three tables.

table #1 is a list of users
table #2 contains users who have trait A
table #3 contains users who have trait B

If I want to find all the users who have trait A or trait B (in one simple sql) I think I'm stuck.

If I do a regular join, the people who don't have trait A won't show up in the result set to see if they have trait B (and vice versa).
But if I do an outer join from table 1 to tables 2 and 3, I get all the rows in table 1 regardless of the rest of my where clause specifying a requirement against tables 2 or 3.

Before you come up with multiple sqls and temp tables and whatnot, this program is far more complex, this is just the simple case. It dynamically creates the sql based on lots of external factors, so I'm trying to make it work in one sql.
I expect there are combinations of in or exists that will work, but I was hoping for some thing simple.
But basically the outer join will always yield all results from table 1, yes?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

原野 2024-08-15 03:06:47
SELECT *
  FROM table1
LEFT OUTER
  JOIN table2
    ON ...
LEFT OUTER
  JOIN table3
    ON ...
 WHERE NOT (table2.pk IS NULL AND table3.pk IS NULL)

或者如果你想偷偷摸摸:

 WHERE COALESCE(table2.pk, table3.pk) IS NOT NULL

但对于你的情况,我只是建议:

SELECT *
  FROM table1
 WHERE table1.pk IN (SELECT fk FROM table2)
    OR table1.pk IN (SELECT fk FROM table3)

或者可能更有效:

SELECT *
  FROM table1
 WHERE table1.pk IN (SELECT fk FROM table2 UNION (SELECT fk FROM table3)
SELECT *
  FROM table1
LEFT OUTER
  JOIN table2
    ON ...
LEFT OUTER
  JOIN table3
    ON ...
 WHERE NOT (table2.pk IS NULL AND table3.pk IS NULL)

or if you want to be sneaky:

 WHERE COALESCE(table2.pk, table3.pk) IS NOT NULL

but for you case, i simply suggest:

SELECT *
  FROM table1
 WHERE table1.pk IN (SELECT fk FROM table2)
    OR table1.pk IN (SELECT fk FROM table3)

or the possibly more efficient:

SELECT *
  FROM table1
 WHERE table1.pk IN (SELECT fk FROM table2 UNION (SELECT fk FROM table3)
戏剧牡丹亭 2024-08-15 03:06:47

如果您真的只想要具有一种或另一种特征的用户列表,那么:

SELECT userid FROM users
  WHERE userid IN (SELECT userid FROM trait_a UNION SELECT userid FROM trait_b)

具体到外连接,longneck 的答案看起来就像我正在写的那样。

If you really just want the list of users that have one trait or the other, then:

SELECT userid FROM users
  WHERE userid IN (SELECT userid FROM trait_a UNION SELECT userid FROM trait_b)

Regarding outerjoin specifically, longneck's answer looks like what I was in the midst of writing.

烟若柳尘 2024-08-15 03:06:47

我认为你可以在这里做一个联盟。

I think you could do a UNION here.

独享拥抱 2024-08-15 03:06:47

我可以建议:

SELECT columnList FROM Table1 WHERE UserID IN (SELECT UserID FROM Table2)
UNION
SELECT columnList FROM Table1 WHERE UserID IN (SELECT UserID FROM Table3)

May I suggest:

SELECT columnList FROM Table1 WHERE UserID IN (SELECT UserID FROM Table2)
UNION
SELECT columnList FROM Table1 WHERE UserID IN (SELECT UserID FROM Table3)
夜无邪 2024-08-15 03:06:47

像这样的东西会起作用吗?请记住,根据表的大小,左外连接在性能方面可能会非常昂贵。

Select *
from table1
where userid in (Select t.userid
From table1 t
left outer join table2 t2 on t1.userid=t2.userid and t2.AttributeA is not null
left outer join table3 t3 on t1.userid=t3.userid and t3.AttributeB is not null
group by t.userid) 

Would something like this work? Keep in mind depending on the size of the tables left outer joins can be very expensive with regards to performance.

Select *
from table1
where userid in (Select t.userid
From table1 t
left outer join table2 t2 on t1.userid=t2.userid and t2.AttributeA is not null
left outer join table3 t3 on t1.userid=t3.userid and t3.AttributeB is not null
group by t.userid) 
哀由 2024-08-15 03:06:47

如果您想要的只是用户的 ID,那么

SELECT UserId From Table2
UNION
SELECT UserId From Table3

就完全足够了。

如果您想从表 1 中获取有关这些用户的更多信息,可以将上面的 SQL 连接到表 1:

SELECT <list of columns from Table1>
FROM Table1 Join (
    SELECT UserId From Table2
    UNION
    SELECT UserId From Table3) User on Table1.UserID = Users.UserID

If all you want is the ids of the users then

SELECT UserId From Table2
UNION
SELECT UserId From Table3

is totally sufficient.

If you want some more infos from Table1 on these users, you can join the upper SQL to Table 1:

SELECT <list of columns from Table1>
FROM Table1 Join (
    SELECT UserId From Table2
    UNION
    SELECT UserId From Table3) User on Table1.UserID = Users.UserID
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文