MariaDB:LEFT OUTER JOIN不返回行 [英] MariaDB: LEFT OUTER JOIN does not return row

问题描述

我已经尝试了各种类型的JOINS，但是无法使这个简单的查询起作用.无论如何，即使在表b中没有相应的条目，我都希望得到表a的结果.我试过了:

I already tried various types of JOINS but I am not able to get this simple query to work. I would like to have the result of table a in any case, even if there is no corresponding entry in table b. I tried:

SELECT a.user_id,
       a.user_name,
       b.first_name
FROM   users a
LEFT OUTER JOIN members b
ON a.member_uid = b.uid
WHERE  (a.user_name = 'TEST'
    AND b.active = 1)

在这种情况下，b中没有b.active = 1的条目.但是我假设将返回a中所有想要的列，而b中的列将为空.但是，当在MariaDB的SQL窗口中运行此查询时，将返回零行.

In this case, there is no entry in b that has b.active = 1. But I assumed that all wanted columns from a would be returned and the column from b would be null. But when running this query in the SQL window of the MariaDB, zero rows are returned.

任何帮助将不胜感激！

推荐答案

左外部联接将从表a中获取所有行/数据，无论它们在表b中是匹配还是不匹配.但是，您再次通过在where子句中放置条件来过滤掉数据.由于b中没有b.active = 1的条目，因此将没有输出.从查询中删除b.active = 1，如下所示:

Left Outer Join will get all the rows/data from table a whether they are matching or not-matching in table b. But you are again filtering out the data by putting conditions in where clause. Since, there is no entry in b that has b.active = 1 so there will be no output. Remove b.active = 1 from the query, like this :

SELECT a.user_id,
   a.user_name,
   b.first_name
FROM   users a
LEFT OUTER JOIN members b
ON a.member_uid = b.uid
WHERE a.user_name = 'TEST';

这篇关于MariaDB:LEFT OUTER JOIN不返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！