左联接返回的记录多于表1中的数据，并添加了其他数据 [英] Left Join returning more records than in Table 1 and adding in additional data

问题描述

我是MySQL的新手，以前在MS Access中做过所有事情.我试图将2个表连接在一起，以便可以显示Table1中的所有记录，并添加Table2中的某些列.

I am new to MySQL having previously done everything in MS Access. I am trying to join together 2 tables so that I can show all of the records from Table1 and add in certain columns from Table2.

我可以使用

SELECT Table1.Name, Table1.Address, Table1.TelephoneNumber
FROM Table1
LEFT JOIN Table2
ON Table1.TelephoneNumber=Table2.PhoneNumber

Table1拥有3900条记录，而Table2则接近700万

Table1 has 3900 records and Table2 almost 7million

然后，我想从表2中添加(例如)PostTown和PostCode.这样我的查询就会返回

I then want to add in (for example) PostTown and PostCode from Table2. So that my query will return

Table1.Name，Table1.Address，Table1.TelephoneNumber，Table2.PostTown，Table2.PostCode

Table1.Name, Table1.Address, Table1.TelephoneNumber, Table2.PostTown, Table2.PostCode

我如何使查询仅返回Table1中的所有内容，但显示来自Table2的匹配项(其中有一些则为空白)和没有空白的项. Table2.PhoneNumber中有一些空白值，我认为这些值重复了我的结果，因为它返回了将近一百万行...

How do I make the query only return everything in Table1 but show matches from Table2 where it has some and blanks where it hasn't. There are some blank values in the Table2.PhoneNumber which I think are duplicating in my results as it returns almost a million rows...

推荐答案

您的数据中包含空格(不能为null):

You have blanks in your data (not nulls):

SELECT Table1.Name, Table1.Address, Table1.TelephoneNumber
FROM Table1
LEFT JOIN Table2
  ON Table1.TelephoneNumber = Table2.PhoneNumber
  AND Table1.TelephoneNumber != ''

检查NOT NULL将无济于事，因为null不等于null(而空白等于空白)

Checking for NOT NULL won't help, because null is not equal to null (whereas blank is equal to blank)

这篇关于左联接返回的记录多于表1中的数据，并添加了其他数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！