这个简单的联合选择查询有什么问题?(mysql)

I have this query:

我有这个查询:

SELECT
    COALESCE(CONCAT(Ar.usaf, '-', Ar.wban),"NONE") AS TABLE_NAME
FROM
    `metadata`.`ISH-HISTORY_HASPOS` A
 INNER JOIN `metadata`.`Artificial` Ar ON (Ar.id = A.mergeId)
WHERE
    A.usaf = usaf
AND A.wban = wban;

When no join occurs and the result is NULL I expected the query to COALESCE the null result with "NONE", however I still get NULL. How would I make it such that "NONE" is returned instead of NULL?

当没有连接时，结果为NULL时，我期望查询将NULL结果合并为“NONE”，但我仍然得到NULL。如何使“NONE”被返回而不是NULL?

I neglected to explain an important point: usaf and wban are IN variables to a stored procedure. Here is my final (working) version. Thanks Rolando

我没有解释一个要点:usaf和wban是存储过程的变量。这是我的最终版本。由于罗兰多

BEGIN
SET @usaf = usaf;
SET @wban = wban;

SELECT
    COALESCE(CONCAT(Ar.usaf, '-', Ar.wban),"NONE") AS TABLE_NAME
FROM
    `metadata`.`ISH-HISTORY_HASPOS` A
 INNER JOIN `metadata`.`Artificial` Ar ON (Ar.id = A.mergeId AND    A.usaf = @usaf AND A.wban = @wban)
LIMIT 1;

END

2 个解决方案

#1

Supply the Ar alias in the WHERE clause

在WHERE子句中提供Ar别名

SELECT
    COALESCE(CONCAT(Ar.usaf, '-', Ar.wban),"NONE") AS TABLE_NAME
FROM
    `metadata`.`ISH-HISTORY_HASPOS` A
 INNER JOIN `metadata`.`Artificial` Ar ON (Ar.id = A.mergeId)
WHERE
    A.usaf = Ar.usaf
AND A.wban = Ar.wban;

Looking at this query, you could improve the JOIN even more in three(3) ways

查看这个查询，您可以通过三种方式改进连接

First, add the WHERE clause elements into the ON section of the JOIN

首先，将WHERE子句元素添加到连接的ON部分

Second, make it a LEFT JOIN so as to accommodate NULL situations

其次，将它设置为左连接，以便适应NULL情况

SELECT
    COALESCE(CONCAT(Ar.usaf, '-', Ar.wban),"NONE") AS TABLE_NAME
FROM
    `metadata`.`ISH-HISTORY_HASPOS` A
    LEFT JOIN `metadata`.`Artificial` Ar
    ON (Ar.id = A.mergeId) AND A.usaf = Ar.usaf AND A.wban = Ar.wban;

Third, add these indexes

第三,添加这些索引

ALTER TABLE `metadata`.`ISH-HISTORY_HASPOS` ADD INDEX (mergeId,usaf,wban);
ALTER TABLE `metadata`.`Artificial` ADD INDEX (id,usaf,wban);

Give it a Try !!!

试试看!!!

#2

The query never considers a NULL value of usaf and wban because of the where clause:

查询从不考虑usaf和wban的空值，因为where子句:

WHERE A.usaf = usaf AND A.wban = wban;

When these columns are NULL, then the row is filtered out. This is even true for the tautology a.usaf = a.usaf. That statement returns FALSE (well, NULL) when a.usaf is NULL.

当这些列为空时，行就被过滤掉了。这对于同义重复a来说也是成立的。美国空军= a.usaf。当a。美国空军是NULL。

Remove the where clause and the query will probably work as you expect.

删除where子句，查询可能会像您预期的那样工作。

As a second comment . . . do you want an inner join or a left outer join?

作为第二个评论……你想要内连接还是左外连接?

#1