LEFT JOIN 關鍵字會從左表 (table_name1) 那裡返回所有的行,即使在右表 (table_name2) 中沒有匹配的行。
RIGHT JOIN 關鍵字會右表 (table_name2) 那裡返回所有的行,即使在左表 (table_name1) 中沒有匹配的行。
Create Table job
insert into `department` (`ID`, `CODE`, `NAME`) values('1','Java R&D','Java R&D'); insert into `department` (`ID`, `CODE`, `NAME`) values('2','Mysql R&D','Mysql R&D'); insert into `department` (`ID`, `CODE`, `NAME`) values('3','Interface R&D','Interface R&D'); insert into `department` (`ID`, `CODE`, `NAME`) values('4','Android R&D','Android R&D'); insert into `department` (`ID`, `CODE`, `NAME`) values('5','IOS R&D','IOS R&D');
insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('1','Peter','Qiu','Qiu','1'); insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('2','Tom','Lee','Lee','2'); insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('3','Garfield','Wang','Wang','3'); insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('4','哆啦A夢','Jone','Jone',NULL); insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('5','Westlife','西城男孩','Westlife','8');
/*以Job表進行左連接,此時數據主要以Job表為主,關聯department表,如果Job的dep_id為null或者在department表中 無對應的Id匹配對應的字段自動填充null*/ SELECT * FROM Job table_name1 LEFT JOIN department table_name2 ON table_name1.`dep_id`= table_name2.`ID`;查出來的數據 id FirstName LastName LoginName dep_id ID CODE NAME 1 Peter Qiu Qiu 1 1 Java R&D Java R&D 2 Tom Lee Lee 2 2 Mysql R&D Mysql R&D 3 Garfield Wang Wang 3 3 Interface R&D Interface R&D 4 哆啦A夢 Jone Jone (NULL) (NULL) (NULL) (NULL) 5 Westlife 西城男孩 Westlife 8 (NULL) (NULL) (NULL)
/*以Job表進行右連接,此時數據主要以department表為主,關聯Job表,如果Job的dep_id為null或者在department表中 無對應的Id匹配對應的字段自動填充null*/ SELECT * FROM Job table_name1 RIGHT JOIN department table_name2 ON table_name1.`dep_id`= table_name2.`ID`;
查出來的數據
id FirstName LastName LoginName dep_id ID CODE NAME 1 Peter Qiu Qiu 1 1 Java R&D Java R&D 2 Tom Lee Lee 2 2 Mysql R&D Mysql R&D 3 Garfield Wang Wang 3 3 Interface R&D Interface R&D (NULL) (NULL) (NULL) (NULL) (NULL) 4 Android R&D Android R&D (NULL) (NULL) (NULL) (NULL) (NULL) 5 IOS R&D IOS R&D