SQL用where子句离开JOIN并没有返回所有结果 [英] SQL left JOIN with where clause not returning all results

问题描述

我有2个使用ID联接的表.如果该ID位于表#2中，我希望显示主表中的所有数据并进行匹配，以在输出中显示更多的列.目前适用于

I have 2 tables that I join using an ID. I want all the data from my main table to show and match if that ID is in table #2 to show a few more columns in my output. That currently works with

select table1.id, table1.name, table1.phone, table1.address, 
table2.loyalcustomer, table2.loyaltynumb, table2.loyaltysince from table1
left join table2
ON table1.id = table2.table1id

我想做的是同一件事，但是在table2.loyalcustomer！='Yes'中添加了WHERE子句.当我这样做时，它不会返回主表(table1)中的所有数据，而是仅显示table1和table2之间的匹配项.另外，table2并没有全部信息，只有插入表中的信息.

What I'm trying to do is the same thing, but add a WHERE clause to table2.loyalcustomer != 'Yes'. When I do that, it doesn't return all the data from my main table (table1), but instead only shows what matches between table1 and table2. Also, table2 does not have all the info, only what was inserted into the table.

select table1.id, table1.name, table1.phone, table1.address, 
table2.loyalcustomer, table2.loyaltynumb, table2.loyaltysince from table1 
left join table2
ON table1.id = table2.table1id
WHERE table2.loyalcustomer != 'Yes'

曾经读过关于不同的联接的信息，但是我一直在阅读的是我的where语句可能与我的联接相矛盾，我不确定如何解决这个问题.

Been reading about different joins but what i've been reading is that my where statement may be contradicting my join and I'm not sure how to resolve that.

SQL DB:Postgres

SQL DB: Postgres

推荐答案

问题出在您的WHERE子句上.小心左联接！

The problem is on your WHERE clause. Be carefull with LEFT JOINS !

在表上进行左联接时，该表将不会像过滤一个内联接那样过滤结果.这是因为您接受LEFT JOIN TABLE返回整个NULL行.

When you do a LEFT JOIN on a TABLE, this table wont filter the results as if it was an INNER JOIN. This is because you accept your LEFT JOIN TABLE to return entire NULL rows.

但是，当您说..."table2.loyalcustomer！='Yes'"时，您正在WHERE子句中的"LEFT JOINED TABLE"中使用列.当table2.loyalcustomer不为null时，此子句有效，但如果table2.loyalcustomer为NULL，则此子句不起作用.

However, you are using a COLUMN from your "LEFT JOINED TABLE" in your WHERE clause when you say... "table2.loyalcustomer != 'Yes'" . This clause works when table2.loyalcustomer is not not null but it DOESN'T work if table2.loyalcustomer is NULL.

所以在这里它是正确的方法:

So here it goes the right way to do it :

选择table1.id，...来自表1左连接table2 ON table1.id = table2.table1id和table2.loyalcustomer！='是'

这是一种替代方法...

Here it goes an alternative way to do it...

选择table1.id，...来自表1左联接table2 ON table1.id = table2.table1idISNULL(table2.loyalcustomer，'')在哪里=='是'

要恢复:NULL！='是'不起作用.您需要不同于null的内容来评估您的表情.

To resume : NULL != 'Yes' doesn't work. You need something different from null to evaluate your expression.

这篇关于SQL用where子句离开JOIN并没有返回所有结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！