如果不存在匹配项 (cust_id = 73)，则指定给“my_alias”表的 LEFT（外部）JOIN 仍应返回数据。当然，您的内部 JOIN 取决于从此表返回的匹配项 (cdm.alias_id = c.alias_id)。我认为“c.cust_id = 73”应该成为 WHERE 子句的一部分，并且下一个 JOIN 的条件之一应该在 LEFT JOIN 中使用。
本质上，您的 ON 子句会缩短您的查询并限制您的结果。在不知道（确切地）您想要完成什么（或者您的表是什么样子）的情况下，请尝试这样的操作：
SELECT NVL(cdm.dest_id, s.dest_id)
FROM my_approval s
INNER JOIN my_alias c ON c.alias_id = s.dest_id
INNER JOIN my_destalias cdm ON cdm.alias_id = s.dest_id
WHERE s.type = 1
AND c.cust_id = 73;
这会将所有文字值从 ON 子句中取出并放入 WHERE 子句中。根据您的帖子，您尝试返回 cdm.alias_id 等于 c.alias_id 和 s.dest_id 的行。我已将这些条件放入您的 ON 条款中。根据这三个表的相互依赖程度，我选择使用两个 INNER JOIN（无 LEFT JOIN）。
First of all, those two queries look like they are trying to accomplish the same thing. The first one should not execute, because you cannot have two ON clauses for a JOIN. The second query looks just like the first, except the offending ON clause has been moved to the proper place.
As for how it works, this query will select rows FROM the "my_approval" table. It will display the dest_id from the "my_destalias" table. However if that value is NULL, it will display the dest_id from "my_approval" instead.
Rows will only be returned if there is a matching row in the "my_destalias" table (based-on alias_id matching with dest_id and alias_id from "my_alias"), as the plain "JOIN" keyword specifies a join of equality (aka INNER JOIN). It should be noted, that rows in this query will only be returned if my_destalias.alias_id is equal to BOTH my_approval.dest_id AND my_alias.alias_id.
The LEFT (outer) JOIN specified to the "my_alias" table should still return data if there is not a match present (cust_id = 73). Of course, your inner JOIN is dependent on a match being returned from this table (cdm.alias_id = c.alias_id). I'm thinking that the "c.cust_id = 73" should be a part of your WHERE clause instead, and one of the next JOIN's conditions should be used in your LEFT JOIN, instead.
Essentially, your ON clauses are short-circuiting your query and limiting your results. Without knowing (exactly) what you're trying to accomplish (or what your tables look like), give something like this a try:
SELECT NVL(cdm.dest_id, s.dest_id)
FROM my_approval s
INNER JOIN my_alias c ON c.alias_id = s.dest_id
INNER JOIN my_destalias cdm ON cdm.alias_id = s.dest_id
WHERE s.type = 1
AND c.cust_id = 73;
This takes all of your literal values out of your ON clauses, and into your WHERE clause. Based on your post, you are trying to return rows where cdm.alias_id is equal to both c.alias_id and s.dest_id. I have put those conditions in your ON clauses. Based-on how inter-dependent these three tables are, I opted to go with two INNER JOINs (no LEFT JOIN).
select nvl(cdm.dest_id, s.dest_id)
from my_approval s
left join my_alias c
join my_destalias cdm
on c.cust_id = 73 on cdm.alias_id = c.alias_id
and cdm.alias_id = s.dest_id
and s.type = 1;
select nvl(cdm.dest_id, s.dest_id)
from my_approval s
left join my_alias c
on c.cust_id = 73
join my_destalias cdm
on cdm.alias_id = c.alias_id
and cdm.alias_id = s.dest_id
and s.type = 1;
select nvl(cdm.dest_id, s.dest_id)
from my_approval s
left join my_alias c
join my_destalias cdm
on c.cust_id = 73 on cdm.alias_id = c.alias_id
and cdm.alias_id = s.dest_id
and s.type = 1;
It works like the left join the join condition c.cust_id = 73 works like a left join but only if it finds the matched record then it will return the rows.
2nd Query
select nvl(cdm.dest_id, s.dest_id)
from my_approval s
left join my_alias c
on c.cust_id = 73
join my_destalias cdm
on cdm.alias_id = c.alias_id
and cdm.alias_id = s.dest_id
and s.type = 1;
This is pretty simple I guess. It works based on the join condition (i.e) 1st left join with my_alias and join with my_destalias. Thx for all those who responded for this question.
发布评论
评论（2）
首先，这两个查询看起来像是在尝试完成同一件事。第一个不应执行，因为 JOIN 不能有两个 ON 子句。第二个查询看起来与第一个查询类似，只是有问题的 ON 子句已移至正确的位置。
至于它是如何工作的，此查询将从“my_approval”表中选择行。它将显示“my_destalias”表中的 dest_id。但是，如果该值为 NULL，它将显示“my_approval”中的 dest_id。
仅当“my_destalias”表中存在匹配行时才会返回行（基于与“my_alias”中的 dest_id 和 alias_id 匹配的 alias_id），因为普通的“JOIN”关键字指定相等的联接（也称为 INNER JOIN） 。应该注意的是，只有当 my_destalias.alias_id 等于 my_approval.dest_id 和 my_alias.alias_id 时，才会返回此查询中的行。
如果不存在匹配项 (cust_id = 73)，则指定给“my_alias”表的 LEFT（外部）JOIN 仍应返回数据。当然，您的内部 JOIN 取决于从此表返回的匹配项 (cdm.alias_id = c.alias_id)。我认为“c.cust_id = 73”应该成为 WHERE 子句的一部分，并且下一个 JOIN 的条件之一应该在 LEFT JOIN 中使用。
本质上，您的 ON 子句会缩短您的查询并限制您的结果。在不知道（确切地）您想要完成什么（或者您的表是什么样子）的情况下，请尝试这样的操作：
这会将所有文字值从 ON 子句中取出并放入 WHERE 子句中。根据您的帖子，您尝试返回 cdm.alias_id 等于 c.alias_id 和 s.dest_id 的行。我已将这些条件放入您的 ON 条款中。根据这三个表的相互依赖程度，我选择使用两个 INNER JOIN（无 LEFT JOIN）。
First of all, those two queries look like they are trying to accomplish the same thing. The first one should not execute, because you cannot have two ON clauses for a JOIN. The second query looks just like the first, except the offending ON clause has been moved to the proper place.
As for how it works, this query will select rows FROM the "my_approval" table. It will display the dest_id from the "my_destalias" table. However if that value is NULL, it will display the dest_id from "my_approval" instead.
Rows will only be returned if there is a matching row in the "my_destalias" table (based-on alias_id matching with dest_id and alias_id from "my_alias"), as the plain "JOIN" keyword specifies a join of equality (aka INNER JOIN). It should be noted, that rows in this query will only be returned if my_destalias.alias_id is equal to BOTH my_approval.dest_id AND my_alias.alias_id.
The LEFT (outer) JOIN specified to the "my_alias" table should still return data if there is not a match present (cust_id = 73). Of course, your inner JOIN is dependent on a match being returned from this table (cdm.alias_id = c.alias_id). I'm thinking that the "c.cust_id = 73" should be a part of your WHERE clause instead, and one of the next JOIN's conditions should be used in your LEFT JOIN, instead.
Essentially, your ON clauses are short-circuiting your query and limiting your results. Without knowing (exactly) what you're trying to accomplish (or what your tables look like), give something like this a try:
This takes all of your literal values out of your ON clauses, and into your WHERE clause. Based on your post, you are trying to return rows where cdm.alias_id is equal to both c.alias_id and s.dest_id. I have put those conditions in your ON clauses. Based-on how inter-dependent these three tables are, I opted to go with two INNER JOINs (no LEFT JOIN).
回答我的问题...
第一个查询
它的工作方式类似于左连接，连接条件
c.cust_id = 73的工作方式类似于左连接，但仅当它找到匹配的记录时才会返回行。第二个查询
我想这很简单。它根据连接条件起作用
（即）第一个
left join与my_alias和join与my_destalias。感谢所有回答这个问题的人。
Answer for my question...
1st Query
It works like the left join the join condition
c.cust_id = 73works like a left join but only if it finds the matched record then it will return the rows.2nd Query
This is pretty simple I guess. It works based on the join condition
(i.e) 1st
left joinwithmy_aliasandjoinwithmy_destalias.Thx for all those who responded for this question.