LEFT JOIN不会返回左侧表中的所有记录 [英] LEFT JOIN does not return all the records from the left side table

问题描述

SELECT d.mt_code,
       d.dep_name,
       d.service_name,
       COUNT(*)
FROM DepartmentService AS d
LEFT JOIN tbl_outgoing AS t ON d.mt_code = t.depCode
WHERE d.service_type = 'MT'
  AND t.smsc = "mobitelMT"
  AND t.sendDate BETWEEN '2014-07-01' AND '2014-07-02'
GROUP BY d.mt_code

DepartmentService表包含有关提供服务的部门的详细信息. tbl_outgoing表包含由客户完成的针对特定服务的所有交易.在WHERE子句中，应满足两个自助餐厅，即service_type = 'MT' and smsc = "newMT".我想得到一个报告，该报告显示给定期间内所有与交易有关的部门.我使用LEFT JOIN是因为我想得到所有部门. SQL可以正常工作并获得我想要的结果，除了

DepartmentService table has details about departments that offer services. tbl_outgoing table contains all the transactions happened for a particular service which are done by customers. In the WHERE clause two cafeterias should be fulfilled which are service_type = 'MT' and smsc = "newMT". I want to get a report which shows all the departments with the transactions for a given period. I have used a LEFT JOIN because I want to get all the departments. SQL works fine and get the result I want except,

在特定时期内没有针对特定服务的交易时，部门也将被忽略.我想做的是在结果集和COUNT(*)栏中显示部门为0.

When there are no transactions for a particular service for a particular period, The department is also ignored. What I want to do is show the department in the resultset and COUNT(*) column to be 0.

我该怎么做?

推荐答案

问题可能是您正在使用where条件对联接的表进行过滤，这也会过滤联接中不匹配的部门服务，将过滤器移到联接中，并将过滤器仅留在where子句中的d上:

The problem could be that you are filtering on the joined table using the where condition which will filter also the department services which don"t have a match in the join, move the filtering in the join and leave only the filters on d in the where clause:

SELECT d.mt_code,
   d.dep_name,
   d.service_name,
   COUNT(t.id)
FROM DepartmentService AS d
LEFT JOIN tbl_outgoing AS t 
  ON d.mt_code = t.depCode 
    AND t.smsc = "mobitelMT"
    AND t.sendDate BETWEEN '2014-07-01' AND '2014-07-02'
WHERE d.service_type = 'MT'
GROUP BY d.mt_code

---

为解释这种情况的发生原因，我将带您逐步了解一下查询和查询所发生的情况，作为数据集，我将使用以下方法:

---

To explain why this happens I'll walk you through what happens with your query and with my query, as dataset I'll use this:

states
 ____ _________ 
| id | state   |
|  1 | Germany |
|  2 | Italy   |
|  3 | Sweden  |
|____|_________|

cities

 ____ ________ ___________ ____________
| id | city   | state_fk  | population |
|  1 | Berlin |        1  |         10 |
|  2 | Milan  |        2  |          5 |
|____|________|___________|____________|

首先，我会查询您的查询.

First I'll go through your query.

SELECT s.id, s.state, c.population, c.city
FROM states s
LEFT JOIN cities c
ON c.state_fk = s.id
WHERE c.population < 10

因此，请不要一步一步走，选择三个州，并与以结尾的城市结尾的州联结起来:

So le't go step by step, you select the three states, left join with cities ending up with:

 ____ _________ ____________ ________
| id | state   | population | city   |
|  1 | Germany |         10 | Berlin |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|

您使用WHERE c.population < 10过滤人口，此时您的位置是这样:

The you filter the population using WHERE c.population < 10, at this point your left with this:

 ____ _________ ____________ ________
| id | state   | population | city   |
|  2 | Italy   |          5 | Milan  |
|____|_________|____________|________|

您放弃了德国，因为柏林人口为10.但您也丢失了具有NULL的瑞典，如果要保留空值，则应在查询中指定它:

You loose Germany because Berlin population was 10 but you lost also Sweden which had NULL, if you wanted to keep the nulls you should have specified it in the query:

WHERE (c.population < 10 OR IS NULL c.population)

哪个返回:

 ____ _________ ____________ ________
| id | state   | population | city   |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|

---

现在查询:

---

Now my query:

SELECT s.id, s.state, c.population, c.city
FROM states s
LEFT JOIN cities c
ON c.state_fk = s.id
  AND c.population < 10

在将两者合并之前，我们对表格城市进行过滤(使用ON之后的AND c.population < 10条件)，剩下的是:

Before joining the two, we filter the table cities (using the AND c.population < 10 condition after the ON), what remains is:

 ____ ________ ___________ ____________
| id | city   | state_fk  | population |
|  2 | Milan  |        2  |          5 |
|____|________|___________|____________|

因为米兰是唯一一个人口少于10岁的城市，所以现在，我们可以将两个表合并在一起:

Because Milan is the only city with population minor than 10, now we can join the two tables:

 ____ _________ ____________ ________
| id | state   | population | city   |
|  1 | Germany |       NULL | NULL   |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|

如您所见，由于过滤条件仅仅应用于城市表，因此左侧表中的数据仍然存在.

As you can see the data from the left table stays because the filtering condition was applied only to the cities table.

结果集会根据您要实现的目标而变化，例如，如果您要过滤德国(因为柏林的人口未满10岁，并且保留瑞典)，则应使用第一种方法添加IS NULL条件，如果您想要要保留它，您应该使用第二种方法并预先过滤左联接右侧的表.

The result set changes depending on what you want to achieve, if for example you do want to filter Germany because Berlin has population minor than 10 and keep Sweden you should use the first approach adding the IS NULL condition, if you want to keep it instead, you should use the second approach and pre filter the table on the right of the left join.

这篇关于LEFT JOIN不会返回左侧表中的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！