是否可以限制外连接的结果?
我有一个场景,我需要跨三个表进行联接。
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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
或者如果你想偷偷摸摸:
但对于你的情况,我只是建议:
或者可能更有效:
or if you want to be sneaky:
but for you case, i simply suggest:
or the possibly more efficient:
如果您真的只想要具有一种或另一种特征的用户列表,那么:
具体到外连接,longneck 的答案看起来就像我正在写的那样。
If you really just want the list of users that have one trait or the other, then:
Regarding outerjoin specifically, longneck's answer looks like what I was in the midst of writing.
我认为你可以在这里做一个联盟。
I think you could do a UNION here.
我可以建议:
May I suggest:
像这样的东西会起作用吗?请记住,根据表的大小,左外连接在性能方面可能会非常昂贵。
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.
如果您想要的只是用户的 ID,那么
就完全足够了。
如果您想从表 1 中获取有关这些用户的更多信息,可以将上面的 SQL 连接到表 1:
If all you want is the ids of the users then
is totally sufficient.
If you want some more infos from Table1 on these users, you can join the upper SQL to Table 1: