加入前可以选择吗?

发布于 2024-10-06 19:33:31 字数 791 浏览 7 评论 0原文

我想要左连接两个表。我的问题是数据库中不存在“右侧”的表。我需要在查询期间创建它。更详细地说,“右”表是通过从现有表中提取(select ... where ...)某些行来获得的。

我认为解决方案是先join,然后select。我认为它只会比 electjoin 慢,但我意识到它根本不起作用。

例如:

左表:

| Dog | 1 |
| Cat | 0 |

右表:

| Dog | Paul | yes |
| Cat | Carin | no |

我只想使用第二个表中第二列包含“Carin”的行。因此,如果我在选择这些行后加入,我会得到:

| Dog | 1 | Null | Null|
| Cat | 0 | Carin | no |

但如果我第一次加入,我会得到:

| Dog | 1 | Paul | yes |
| Cat | 0 | Carin | no |

然后如果我选择带有 Carin 的行,我会得到:

| Cat | 0 | Carin | no |

所以,你会看到结果是不同的。在第一种情况下,我得到 2 行,在第二种情况下,我只得到 1 行。

那么,加入之前可以选择吗?

I want to left join two tables. My problem is that the table from the "right" does not exist in the database. I need to create it during the query. In more details, the "right" table is obtained by extraction (select ... where ...) of certain rows from an existing table.

I thought that the solution would be to join first and then to select. I thought that it will be just slower than elect and join but than I realized that it will not work at all.

For example:

Left table:

| Dog | 1 |
| Cat | 0 |

Right table:

| Dog | Paul | yes |
| Cat | Carin | no |

I want to use only those rows from the second table where the second column has "Carin". So, if I join after selection of those rows I get:

| Dog | 1 | Null | Null|
| Cat | 0 | Carin | no |

But if I first join, I get:

| Dog | 1 | Paul | yes |
| Cat | 0 | Carin | no |

And then if I select rows with Carin I get:

| Cat | 0 | Carin | no |

So, you see that result is different. In the first case I get 2 rows and in the second case I got just 1 row.

So, is it possible to select before join?

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

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

发布评论

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

评论(3

殊姿 2024-10-13 19:33:31

我认为您正在谈论引用在定义为连接语句一部分的子查询中创建的字段。类似这样:

SELECT a.id, b.name 
   FROM table AS a
   LEFT JOIN
     (SELECT name FROM table2) AS b
   ON a.id = b.id

在此,您引用 b.name,即使 b.name 仅作为稍后在查询中创建的子查询的一部分存在。

如果这就是您的意思,那么您绝对可以做到。如果不能的话,就没有理由加入子查询。

如果我误解了您的意图,请将您想要运行的实际查询添加到您的问题中,以便我可以更清楚地看到问题。

I think you're talking about referencing fields created in a subquery defined as part of a join statement. Something like this:

SELECT a.id, b.name 
   FROM table AS a
   LEFT JOIN
     (SELECT name FROM table2) AS b
   ON a.id = b.id

In this, you're referencing b.name even though b.name only exists as part of the subquery created later in the query.

If this is what you mean, then yes you absolutely can do that. There would be very little reason to join on a subquery if you couldn't.

If I'm mistaken about your intent, please add the actual query you'd like to run to your question so I can see the problem more clearly.

扮仙女 2024-10-13 19:33:31

您还可以加入一个常量。基本上,您将 WHERE 子句中的表达式移至 ON 子句:

SELECT a.pet, a.neutered, b.name, b.allergic
  FROM a
  LEFT JOIN b
    ON a.pet=b.pet AND b.name='Carin';

You can also join on a constant. Basically, you move the expression in the WHERE clause to the ON clause:

SELECT a.pet, a.neutered, b.name, b.allergic
  FROM a
  LEFT JOIN b
    ON a.pet=b.pet AND b.name='Carin';
无需解释 2024-10-13 19:33:31

Outis 的示例对我不起作用,但当我用 WHERE 替换 AND 时,它就起作用了。

SELECT a.pet, a.neutered, b.name, b.allergic
  FROM a
  LEFT JOIN b
    ON a.pet=b.pet WHERE b.name='Carin';

The example from Outis did not work for me, but this worked when I replaced the AND by a WHERE.

SELECT a.pet, a.neutered, b.name, b.allergic
  FROM a
  LEFT JOIN b
    ON a.pet=b.pet WHERE b.name='Carin';
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文