Left Join query in MySQL returning null rows for all results

Question

I'm having an odd problem with a left join query:

Table 1: Rates

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

Table 2: People

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

This should be fairly simple to left join the data from the PEOPLE table. Here's the query I'm using:

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

I would expect to see a result like this: EXPECTED RESULT

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...

But instead this is the result I actually get: ACTUAL RESULT

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...

EDIT:
Here's a data dump and SQL fiddle of same, which is returning the same result. Above example was a simplified version of this. I have cleared most of the subcon_people_dev table as it is confidential information, but some populated fields should still appear:

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

I find it hard to believe that left join caused that result.. see demo db-fiddle.com/f/tbxMSPi5zArhshEFVnyA3Q/0 .. So i think a IDE cache or application code is the problem here??? — Raymond Nijland
– Raymond Nijland, Commented Oct 26, 2018 at 10:24
Please create a SQL fiddle / DB Fiddle so that we can verify your claim. — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 26, 2018 at 10:25
@DPS Besides you shouldn't be mixing the old comma join syntax with the newer join syntax it's asking for trouble — Raymond Nijland
– Raymond Nijland, Commented Oct 26, 2018 at 10:36
@DPS Forget about mixing. You should not even use comma join syntax EVER! — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 26, 2018 at 10:37

ParanoiK · Accepted Answer · 2018-10-26 12:22:51Z

1

You got nulls because you don't have any matching keys... In subcon_people_dev you don't have rows with subcon_serial (52, 54, 55, 56).

answered Oct 26, 2018 at 12:22

ParanoiK

412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhur Bhaiya · Accepted Answer · 2018-10-26 11:06:09Z

0

You need to change your Join order. In case of Left Join, leftmost table should be the one, from which you want to consider all the rows (whether having a matching row in other tables or not).

select subcon_rates.*, subcon_people_dev.* 
from subcon_people_dev
left join subcon_rates on subcon_people_dev.`serial` = subcon_rates.subcon_serial

answered Oct 26, 2018 at 11:06

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Collectives™ on Stack Overflow

Left Join query in MySQL returning null rows for all results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related