조인 JOIN , 카운트 COUNT , 중복카운트 COUNT DISTINCT 예제

CREATE TABLE mysql_test_a ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstname VARCHAR(30) NOT NULL, 
lastname VARCHAR(30) NOT NULL,  
email VARCHAR(50), 
rm1 VARCHAR(10),
rm2 VARCHAR(20),
reg_date TIMESTAMP 
); 
 
CREATE TABLE mysql_test_b ( 
id INT(6) NOT NULL,
firstname VARCHAR(30) NOT NULL, 
lastname VARCHAR(30) NOT NULL, 
email VARCHAR(50), 
reg_date TIMESTAMP,
in_date varchar(12)
); 
 
 
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`,`rm1`,`rm2`, `reg_date`) VALUES ('1', 'John', 'Doe', 'john.doe@sqltest.net', 'etc1','etc2', CURRENT_TIMESTAMP);
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`,  `reg_date`) VALUES ('2', 'Park', 'Da', '34.doe@sqltest.net' , CURRENT_TIMESTAMP);
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`,`rm1`,`rm2`,  `reg_date`) VALUES ('3', 'Lee', 'Gh', '12.42@sqltest.net', 'etc1','etc2', CURRENT_TIMESTAMP);
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`,   `reg_date`) VALUES ('4', 'Kim', 'Wq', '45.6@sqltest.net', CURRENT_TIMESTAMP);
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`, `reg_date`) VALUES ('5', 'Yoon', 'Xa', 'gf.doe@sqltest.net', CURRENT_TIMESTAMP);
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`, `reg_date`) VALUES ('6', 'Ma', 'Ha', 'df.doe@sqltest.net', CURRENT_TIMESTAMP);
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`,  `reg_date`) VALUES ('7', 'Shin', 'Wq', 'gf.doe@sqltest.net', CURRENT_TIMESTAMP);
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`, `reg_date`) VALUES ('8', 'Joo', 'Ty', 'hhh.doe@sqltest.net', CURRENT_TIMESTAMP);
INSERT INTO `mysql_test_a` (`id`, `firstname`, `lastname`, `email`,  `reg_date`) VALUES ('9', 'Ha', 'As', 'asd.doe@sqltest.net', CURRENT_TIMESTAMP);
 
 
INSERT INTO `mysql_test_b` (`id`, `firstname`, `lastname`, `email`, `reg_date`,`in_date`) VALUES ('1', 'Left', 'Join', 'john.doe@sqltest.net', CURRENT_TIMESTAMP,'202005'); 
INSERT INTO `mysql_test_b` (`id`, `firstname`, `lastname`, `email`, `reg_date`,`in_date`) VALUES ('2', 'Right', 'Join', '34.doe@sqltest.net', CURRENT_TIMESTAMP,'202005');
INSERT INTO `mysql_test_b` (`id`, `firstname`, `lastname`, `email`, `reg_date`,`in_date`) VALUES ('3', 'Full', 'Join', '12.42@sqltest.net', CURRENT_TIMESTAMP,'202006');
INSERT INTO `mysql_test_b` (`id`, `firstname`, `lastname`, `email`, `reg_date`,`in_date`) VALUES ('2', 'Right', 'Join', '34.doe@sqltest.net', CURRENT_TIMESTAMP,'202005');
INSERT INTO `mysql_test_b` (`id`, `firstname`, `lastname`, `email`, `reg_date`,`in_date`) VALUES ('3', 'Cross', 'Test', '12.42@sqltest.net', CURRENT_TIMESTAMP,'202007');
INSERT INTO `mysql_test_b` (`id`, `firstname`, `lastname`, `email`, `reg_date`,`in_date`) VALUES ('3', 'Inner', 'Zzz', '34.doe@sqltest.net', CURRENT_TIMESTAMP,'202005');
INSERT INTO `mysql_test_b` (`id`, `firstname`, `lastname`, `email`, `reg_date`,`in_date`) VALUES ('3', 'Scal', 'Yyy', '12.42@sqltest.net', CURRENT_TIMESTAMP,'202005');