· 7 years ago · Jan 13, 2019, 01:06 AM
1MySQL LEFT JOIN without WHERE clause returns no null rows
2CREATE TABLE IF NOT EXISTS `punch` (
3 `name` varchar(50) NOT NULL,
4 `date` varchar(50) NOT NULL,
5 `duration` int(11) NOT NULL
6) ENGINE=MyISAM DEFAULT CHARSET=latin1;
7
8INSERT INTO `punch` (`name`, `date`, `duration`) VALUES
9('foo', '1', 2),
10('bar', '1', 3),
11('bar', '2', 5),
12('foo', '3', 6),
13('foo', '4', 8),
14('bar', '4', 9);
15
16SELECT * FROM `punch` P1 WHERE P1.date BETWEEN 1 AND 3 ORDER BY P1.name , date;
17
18name date duration
19bar 1 3
20bar 2 5
21foo 1 2
22foo 3 6
23
24name date duration
25bar 1 3
26bar 2 5
27bar 3 null
28foo 1 2
29foo 2 null
30foo 3 6
31
32SELECT * FROM (
33 SELECT DISTINCT date FROM punch WHERE date BETWEEN 1 AND 3
34) P1
35LEFT JOIN (
36 SELECT * FROM punch -- WHERE name = 'bar'
37) P2 ON P1.date=P2.date
38
39ORDER BY P2.name, P1.date
40
41date name date duration
421 bar 1 3
432 bar 2 5
441 foo 1 2
453 foo 3 6
46
47date name date duration
482 NULL NULL NULL
493 NULL NULL NULL
501 bar 1 3
512 bar 2 5
521 foo 1 2
533 foo 3 6
54
55date name date duration
563 NULL NULL NULL
571 bar 1 3
582 bar 2 5
59
60SELECT DISTINCT
61 P1.name,
62 P2.date,
63 (SELECT PP.duration
64 FROM punch PP
65 WHERE P1.name = PP.name
66 AND P2.date = PP.date ) AS duration
67 FROM
68 (SELECT DISTINCT name FROM `punch`) P1,
69 (SELECT DISTINCT date FROM punch)P2
70 WHERE P2.date BETWEEN 1 AND 3
71 ORDER BY P1.name , P2.date
72
73NAME DATE DURATION
74bar 1 3
75bar 2 5
76bar 3 (null)
77foo 1 2
78foo 2 (null)
79foo 3 6
80
81SELECT * FROM (
82 SELECT DISTINCT date FROM punch WHERE date BETWEEN 1 AND 3
83) P1
84LEFT OUTER JOIN (
85 SELECT * FROM punch WHERE name = 'bar'
86) P2 ON P1.date=P2.date
87
88CREATE TABLE days(day_num INT(11));
89
90INSERT INTO days(day_num) VALUES (1),(2),(3),(4),(5);
91
92SELECT p.* FROM days d
93LEFT JOIN punch p
94 ON p.date = d.day_num
95WHERE
96 d.day_num BETWEEN 1 AND 3
97ORDER BY
98 p.name, p.date;