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

问题描述

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

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.

任何帮助将不胜感激！

推荐答案

Left Outer Join 将获取表 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:左外连接不返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！