Thursday, 30 August 2018

MySQL LEFT JOIN without WHERE clause does not return any null rows


Here's my table:


CREATE TABLE IF NOT EXISTS `punch` (
  `name` varchar(50) NOT NULL,
  `date` varchar(50) NOT NULL,
  `duration` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `punch` (`name`, `date`, `duration`) VALUES
('foo', '1', 2),
('bar', '1', 3),
('bar', '2', 5),
('foo', '3', 6),
('foo', '4', 8),
('bar', '4', 9);

I have table with values below:
SELECT * FROM `punch` P1 WHERE P1.date BETWEEN 1 AND 3 ORDER BY P1.name , date;

result:
name    date    duration
bar        1    3
bar        2    5
foo        1    2
foo        3    6

I want to make a report for date 1 until 3 like this:
name    date    duration
bar        1    3
bar        2    5
bar        3    null
foo        1    2
foo        2    null
foo        3    6

I tried this query (note the commented WHERE):
SELECT * FROM (
    SELECT DISTINCT date FROM punch WHERE date BETWEEN 1 AND 3
) P1
LEFT JOIN (
    SELECT * FROM punch -- WHERE name = 'bar'
) P2 ON P1.date=P2.date

ORDER BY P2.name, P1.date

I got result:
date    name    date    duration
1       bar     1       3
2       bar     2       5
1       foo     1       2
3       foo     3       6

I was expecting something like:
date    name    date    duration
2       NULL    NULL    NULL
3       NULL    NULL    NULL
1       bar     1       3
2       bar     2       5
1       foo     1       2
3       foo     3       6

Now, when I remove the commented WHERE, I got result:
date    name    date    duration
3       NULL    NULL    NULL
1       bar     1       3
2       bar     2       5

My question is, why LEFT JOIN above, behaves like INNER JOIN when there's no WHERE clause?
And what is the correct query for my expected report above?
Thanks

Hi something like this gonna work:)
 SELECT DISTINCT
    P1.name,
    P2.date,
    (SELECT PP.duration
     FROM punch PP
       WHERE P1.name = PP.name
         AND P2.date = PP.date ) AS duration
    FROM
      (SELECT DISTINCT name FROM  `punch`) P1,
      (SELECT DISTINCT date FROM punch)P2
        WHERE P2.date BETWEEN 1 AND 3
        ORDER BY P1.name , P2.date

Results:
NAME    DATE    DURATION
bar        1    3
bar        2    5
bar        3    (null)
foo        1    2
foo        2    (null)
foo        3    6


0 comments:

Post a Comment