blog05.sql

-- left join
SELECT
  animal.*,
  SUM(animal_cnt.cnt) AS sum
FROM animal
LEFT JOIN animal_cnt
  ON animal.animal = animal_cnt.animal
    AND animal.type = animal_cnt.type
GROUP BY animal_cnt.animal, animal_cnt.type;

-- right join
SELECT
  animal.*,
  SUM(animal_cnt.cnt) AS sum
FROM animal
RIGHT JOIN animal_cnt
  ON animal.animal = animal_cnt.animal
    AND animal.type = animal_cnt.type
GROUP BY animal_cnt.animal, animal_cnt.type;

-- sub Query
SELECT
  animal.*,
  animal_sum.cnt AS sum
FROM animal
LEFT JOIN (
  SELECT
    animal,
    type,
    SUM(cnt) AS cnt
  FROM animal_cnt
  GROUP BY animal, type
) AS animal_sum
  ON animal.animal = animal_sum.animal
    AND animal.type = animal_sum.type;

-- true or false?
SELECT TRUE = TRUE;
SELECT FALSE = FALSE;
SELECT "" = "";
SELECT NULL = NULL;

-- ifnull
SELECT
  animal.*,
  SUM(animal_cnt.cnt) AS cnt
FROM animal
LEFT JOIN animal_cnt
  ON animal.animal = animal_cnt.animal
    AND IFNULL(animal.type, '') = IFNULL(animal_cnt.type, '')
GROUP BY animal_cnt.animal, animal_cnt.type;

blog05_testData.sql

-- 実験用データ
--  `animal`
CREATE TABLE IF NOT EXISTS `animal` (
  `animal` varchar(16),
  `type` varchar(16),
  `origin` varchar(16)
);

INSERT INTO `animal` (`animal`, `type`, `origin`) VALUES
('inu', NULL, NULL),
('inu', 'shiba', 'japan'),
('inu', 'pug', 'china'),
('inu', 'samoyed', 'russia'),
('neko', NULL, NULL),
('neko', 'mike', 'japan'),
('neko', 'exotic', 'us'),
('neko', 'ragdoll', 'us');


-- `animal_cnt`
CREATE TABLE IF NOT EXISTS `animal_cnt` (
  `animal` varchar(16),
  `type` varchar(16),
  `cnt` int(11)
);

INSERT INTO `animal_cnt` (`animal`, `type`, `cnt`) VALUES
('inu', NULL, 2),
('inu', NULL, 2),
('inu', NULL, 2),
('inu', 'shiba', 2),
('inu', 'shiba', 3),
('inu', 'pug', 2),
('inu', 'pug', 2),
('inu', 'pug', 2),
('inu', 'samoyed', 1),
('inu', 'samoyed', 1),
('inu', 'samoyed', 1),
('neko', NULL, 1),
('neko', 'mike', 1),
('neko', 'mike', 1),
('neko', 'exotic', 1),
('neko', 'exotic', 3),
('neko', 'ragdoll', 1),
('neko', 'ragdoll', 2),
('neko', 'ragdoll', 3);