mysql中的left join查询为所有结果返回空行

zf2sa74q  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(499)

我在左连接查询中遇到了一个奇怪的问题：
表1：费率

Serial, peopleSerial, jobSerial
1, 23, 1
1, 24, 1
1, 25, 1
1, 26, 1

表2：人员

Serial, otherKey, name1, address, etc...
23, 65, John, street, etc...
24, 66, Mike, street, etc...
25, 67, Alex, street, etc...

从people表左连接数据应该相当简单。以下是我使用的查询：

SELECT rates.*, people.* FROM rates
LEFT JOIN people ON people.Serial = Rates.peopleSerial

我希望看到这样的结果：预期结果

Serial, peopleSerial, jobSerial, Serial, otherKey, name1, address, etc...
1, 23, 1, 23, 65, John, Street, etc...
2, 24, 1, 24, 66, Mike, Street, etc...
3, 25, 1, 25, 67, Alex, Street, etc...
4, 26, 1, NULL, NULL, NULL, NULL, etc...

但这是我实际得到的结果：实际结果

Serial, peopleSerial, jobSerial, Serial, otherKey, name1, address, etc...<br>
1, 23, 1, NULL, NULL, NULL, NULL, etc...
2, 24, 1, NULL, NULL, NULL, NULL, etc...
3, 25, 1, NULL, NULL, NULL, NULL, etc...
4, 26, 1, NULL, NULL, NULL, NULL, etc...

编辑：
这里有一个数据转储和相同的sql fiddle，它返回相同的结果。上面的例子是这个的简化版本。我已经清除了subco\u people\u dev表中的大部分内容，因为它是机密信息，但仍应显示一些填充字段：

CREATE TABLE IF NOT EXISTS `subcon_rates` (
  `serial` int(11) NOT NULL AUTO_INCREMENT,
  `subcon_serial` int(11) DEFAULT NULL,
  `job_serial` int(11) DEFAULT NULL,
  `type_serial` int(11) DEFAULT NULL,
  `rate` decimal(10,2) DEFAULT NULL,
  `chargeRate` decimal(10,2) DEFAULT NULL,
  `period` date DEFAULT NULL,
  PRIMARY KEY (`serial`)
) ENGINE=MyISAM AUTO_INCREMENT=999 DEFAULT CHARSET=latin1;

INSERT INTO `subcon_rates` (`serial`, `subcon_serial`, `job_serial`, `type_serial`, `rate`, `chargeRate`, `period`) VALUES
    (134, 52, 27, 1, 10.00, 15.00, '2018-10-14'),
    (138, 56, 27, 1, 25.00, 30.00, '2018-10-14'),
    (136, 55, 27, 1, 20.00, 25.00, '2018-10-14'),
    (139, 54, 27, 1, 35.00, 40.00, '2018-10-14'),
    (140, 52, 27, 1, 10.00, 15.00, '2018-10-07'),
    (141, 56, 27, 1, 25.00, 30.00, '2018-10-07'),
    (142, 55, 27, 1, 20.00, 25.00, '2018-10-07'),
    (143, 54, 27, 1, 35.00, 40.00, '2018-10-07'),
    (153, 54, 27, 1, 10.00, 15.00, '2018-10-21'),
    (152, 55, 27, 1, 10.00, 15.00, '2018-10-21'),
    (866, 52, 27, 1, 10.00, 15.00, '2018-10-21'),
    (150, 52, 27, 1, 10.00, 15.00, '2018-10-21');

CREATE TABLE IF NOT EXISTS `subcon_people_dev` (
  `serial` int(11) NOT NULL AUTO_INCREMENT,
  `subconNumber` varchar(4) DEFAULT NULL,
  `type` text,
  `title` text,
  `name1` text,
  `surname` text,
  `nino` text,
  `ref` text,
  `tradingname` text,
  `crn` text,
  `pName` text,
  `pUTR` text,
  `add1` text,
  `add2` text,
  `add3` text,
  `add4` text,
  `postcode` text,
  `UTR` text,
  `role` text,
  `company` text,
  `insuranceExpire` date DEFAULT NULL,
  `approved` int(11) NOT NULL DEFAULT '0',
  `sortCode` text,
  `accountNumber` text,
  `accountName` text,
  `placcount` text,
  PRIMARY KEY (`serial`),
  UNIQUE KEY `subconNumber` (`subconNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=69 DEFAULT CHARSET=latin1;

INSERT INTO `subcon_people_dev` (`serial`, `subconNumber`, `type`, `title`, `name1`, `surname`, `nino`, `ref`, `tradingname`, `crn`, `pName`, `pUTR`, `add1`, `add2`, `add3`, `add4`, `postcode`, `UTR`, `role`, `company`, `insuranceExpire`, `approved`, `sortCode`, `accountNumber`, `accountName`, `placcount`) VALUES
    (1, '0001', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (2, '0010', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (3, '0011', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (4, '0012', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (5, '0013', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (6, '0014', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (7, '0015', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (8, '0016', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (9, '0017', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (10, '0018', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (11, '0019', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (12, '0002', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (13, '0020', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (14, '0021', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (15, '0022', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (16, '0023', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (17, '0024', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (18, '0025', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (19, '0026', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (20, '0027', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (21, '0028', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (22, '0029', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (23, '0003', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (24, '0030', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (25, '0031', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (26, '0032', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (27, '0033', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (28, '0034', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (29, '0035', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (30, '0036', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (31, '0037', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (32, '0038', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (33, '0039', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (34, '0004', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (35, '0040', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (36, '0041', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (37, '0042', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (38, '0043', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (39, '0044', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (40, '0045', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (41, '0046', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (42, '0047', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (43, '0048', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (44, '0049', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (45, '0005', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (46, '0050', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (47, '0051', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (48, '0052', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (49, '0006', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (50, '0007', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (51, '0008', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (59, '62', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
    (60, '54', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
    (61, '55', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
    (62, '59', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
    (63, '53', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, 0, '', '', '', ''),
    (64, '58', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
    (65, '56', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
    (66, '60', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
    (67, '0061', '1', '', '', '', '', '', '', '', NULL, NULL, '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', ''),
    (68, '0063', '1', '', '', '', '', '', '', '', NULL, NULL, '', '', '', '', '', '', NULL, NULL, NULL, 0, '', '', '', '');

https://www.db-fiddle.com/f/ubwys5uvzy62fjiwjhpwpd/1

mysql

2条答案

按热度按时间
bkhjykvo

bkhjykvo1#

你需要改变你的加入顺序。万一 Left Join ，最左边的表应该是要考虑所有行的表（无论其他表中是否有匹配的行）。

select subcon_rates.*, subcon_people_dev.* 
from subcon_people_dev
left join subcon_rates on subcon_people_dev.`serial` = subcon_rates.subcon_serial
赞(0）分享  回复(0）举报  2021-06-18
mzillmmw

mzillmmw2#

因为没有匹配的密钥，所以有空值。。。在subco\u people\u dev中，没有subco\u serial（52、54、55、56）的行。

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com