How to handle NULL data values?

Question

CREATE TABLE `staff` (
  `StaffID` int NOT NULL AUTO_INCREMENT,
  `Surname` varchar(40) DEFAULT NULL,
  `Given` varchar(40) DEFAULT NULL,
  `DOB` datetime DEFAULT NULL,
  `Sex` char(1) DEFAULT NULL,
  `Joined` datetime DEFAULT NULL,
  `Resigned` datetime DEFAULT NULL,
  `Address` varchar(50) DEFAULT NULL,
  `Suburb` varchar(30) DEFAULT NULL,
  `Postcode` varchar(6) DEFAULT NULL,
  `Phone` varchar(15) DEFAULT NULL,
  `SupervisorID` int DEFAULT NULL,
  `Commission` double DEFAULT NULL,
  `RatePerHour` double DEFAULT NULL,
  PRIMARY KEY (`StaffID`)
);

INSERT INTO `staff` VALUES 
    (1,'VELLA','SARATH AJITH L','1968-09-10 00:00:00','M','2012-04-16 00:00:00','2018-01-10 00:00:00','76 SAUNDERS ST','STH MELBOURNE','3153','8579410',0,0,22.21),
    (2,'MARZELLA','PATRICK MICHAEL','1981-12-03 00:00:00','M','2012-03-09 00:00:00','2018-03-29 00:00:00','126 THE PARADE','NTH CARLTON','3146','93374764',1,0,17.87),
    (3,'HILTON','HARRY RODNEY E','1994-03-18 00:00:00','F','2011-10-18 00:00:00',NULL,'24/49 WALSH ST','BIRREGURRA VIC','3205','97231600',0,0,18.83),
    (4,'JAMIESON','TERRENCE IAN','1967-11-02 00:00:00','F','2012-07-12 00:00:00','2017-09-17 00:00:00','6 LIBRA CRT','ENDEAVOUR HILLS','3764','94392347',1,0,10.23),
    (5,'SANDERS','RICHARD ANTHONY','1986-03-03 00:00:00','M','2013-01-21 00:00:00','2018-05-06 00:00:00','513 TOORONGA RD','COBURG','3146','3001193',1,0,16.02),
    (6,'SUMMERS','TED','1983-08-04 00:00:00','M','2013-05-29 00:00:00','2020-01-08 00:00:00','4/39 BALSTON ST','NORTHCOTE','3802','9998877',1,0,18.75),
    (7,'KNOL','VINCENZO','1968-09-10 00:00:00','F','2012-07-21 00:00:00',NULL,'41 LENNOX ST','NORTHCOTE','3044','98850345',3,0,11.46),
    (8,'PORTELLI','CATHERINE MARY','1981-08-18 00:00:00','M','2012-09-23 00:00:00','2018-06-13 00:00:00','41 LENNOX ST','EAST BURWOOD VIC','3095','94994432',1,0,17.7),
    (9,'KHOR','GLENDA JEAN','1989-04-27 00:00:00','M','2012-09-15 00:00:00','2018-10-29 00:00:00','10 AUSTIN ST','ESSENDON','3040','38502732',1,0,12.33),
    (10,'SCANLON','MICHAEL JOHN','1993-11-26 00:00:00','M','2012-03-18 00:00:00',NULL,'5 NARR MAEN DVE','ESSENDON','3802','93762678',3,0,16.89);

I used the following query:

SELECT StaffID, concat(Given,' ', Surname) as 'full name', Joined, 
    DATEDIFF(Resigned, Joined) AS 'total days' 
FROM staff;

But, for the null values in the resigned column, the total number of days is not calculated. (The null values in the resigned column means staff has not resigned yet.)

Barmar · Answer 1 · 2022-07-21T06:14:52.320

1

Use IFNULL() to provide a default in place of the NULL values.

SELECT StaffID, concat(Given,' ', Surname) as 'full name', Joined, 
    DATEDIFF(IFNULL(Resigned, NOW()), Joined) AS 'total days' 
FROM staff;

To get the longest serving member, add:

ORDER BY `total days` DESC 
LIMIT 1

Notice that backticks are used there; in this place, single quotes are for string literals, not column names or aliases. See When to use single quotes, double quotes, and backticks in MySQL

edited Jul 21 '22 at 06:14

answered Jul 21 '22 at 06:09

Barmar

741,623
53
500
612

You are faster than me, he needs `ORDER BY 'total days' DESC LIMIT 1` as well: [DB Fiddle here](https://www.db-fiddle.com/f/qriDa2fT7CQBKBx6qCb5fQ/2) – Raptor Jul 21 '22 at 06:12
@Raptor True, but that doesn't seem to be the part he's having trouble with and asking how to fix. – Barmar Jul 21 '22 at 06:13

How to handle NULL data values?

1 Answers1