MySQL - 如果不存在等式，如何获得 NULL [英] MySQL - How to get NULL if equality does not exist

问题描述

CREATE TABLE Customer(  
customer_id INT NOT NULL,  
first_name VARCHAR(20),  
last_name VARCHAR(20),  
PRIMARY KEY (customer_id)  
);  
CREATE TABLE Payment(  
customer_id NOT NULL,  
year YEAR,  
payment_amount INT,  
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)  
);  

这是我正在使用的查询:

Here is the query I am using:

SELECT Customer.first_name, Payment.year  
FROM Customer  
LEFT JOIN Payment  
ON Customer.customer_id = Payment.customer_id  
WHERE Payment.year = 2008;  

现在，假设我有三个客户约翰、鲍勃和安妮.约翰和安妮在 2008 年付款，但鲍勃没有付款，因此此查询的结果是:

Now, let's say I had three customer John, Bob, and Anne. John and Anne made payments in 2008 but Bob did not so the result from this query is :

名字年份
约翰 2008
安妮 2008 年

first_name year
John 2008
Anne 2008

但我想要的是:

名字年份
约翰 2008
安妮 2008
鲍勃空

first_name year
John 2008
Anne 2008
Bob NULL

所以我知道如果没有这样的平等payment.year = 2008而不是返回NULL，我需要一行对tenant_id说类似的话，但我不知道该怎么做.谢谢！

So I know I need a line that says something like for a tenant_id if there is no such equality payment.year = 2008 than return NULL, but I am not sure how to do that. Thanks!

推荐答案

WHERE Payment.year = 2008;   

应该

AND Payment.year = 2008;  

WHERE 在 JOIN 之后应用，因此它过滤掉了 NULL

WHERE is applied after the JOIN so it filters out the NULL

这篇关于MySQL - 如果不存在等式，如何获得 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！