SQLite 左外连接 LEFT OUT JOIN
SQLite 中的左外连接首先获取第一张表所有记录，然后将第二张表中匹配的数据放到一起，第二张表中没有匹配的数据则抛弃
语法
SQLite 中的左外连接 ( LEFT OUT JOIN ) 语法如下
例如下面的语句可以将 tbl_language 和 tbl_rank 两个表做一个左外连接
SELECT tbl_language.id,tbl_language.name,tbl_rank.rank FROM tbl_language LEFT OUTER JOIN tbl_rank on tbl_language.name = tbl_rank.name;
范例
先运行下面的 SQL 语句创建测试数据
DROP TABLE IF EXISTS `tbl_language`; DROP TABLE IF EXISTS `tbl_rank`; CREATE TABLE IF NOT EXISTS `tbl_language`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(64) NOT NULL, `url` VARCHAR(128) NOT NULL, `founded_at` DATE, PRIMARY KEY ( `id` ) ); CREATE TABLE IF NOT EXISTS `tbl_rank`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(64) NOT NULL, `month` VARCHAR(7) NOT NULL, `rank` TINYINT NOT NULL, `rate` VARCHAR(32) NOT NULL, PRIMARY KEY ( `id` ) ); INSERT INTO `tbl_language` VALUES (1,'Python','https://www.twle.cn','1991-2-20'), (2,'PHP','http://www.php.net','1994-1-1'), (3,'Ruby','https://www.ruby-lang.org/','1996-12-25'), (4,'Kotlin','http://kotlinlang.org/','2016-02-17');; INSERT INTO `tbl_rank` VALUES (1, 'Python','2018-04',4,'5.083%'), (2, 'PHP','2018-04',6,'4.218%'), (3, 'Ruby','2018-04',11,'2.018%'), (4, 'Java','2018-04',1,'15.777%'), (5, 'Python','2018-03',4,'5.869%'), (6, 'PHP','2018-03',7,'4.010%'), (7, 'Ruby','2018-03',12,'2.744%'), (8, 'Java','2018-03',1,'14.941'), (9, 'Python','2018-02',4,'5.168%'), (10, 'PHP','2018-02',7,'3.420%'), (11, 'Ruby','2018-02',10,'2.534%'), (12, 'Java','2018-02',1,'14.988%');
tbl_language 表中的数据如下
+----+--------+----------------------------+------------+ | id | name | url | founded_at | +----+--------+----------------------------+------------+ | 1 | Python | https://www.twle.cn | 1991-02-20 | | 2 | PHP | http://www.php.net | 1994-01-01 | | 3 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 | | 4 | Scala | http://kotlinlang.org/ | 2016-02-17 | +----+--------+----------------------------+------------+
tbl_rank 表中的数据如下
+----+--------+---------+------+---------+ | id | name | month | rank | rate | +----+--------+---------+------+---------+ | 1 | Python | 2018-04 | 4 | 5.083% | | 2 | PHP | 2018-04 | 6 | 4.218% | | 3 | Ruby | 2018-04 | 11 | 2.018% | | 4 | Java | 2018-04 | 1 | 15.777% | | 5 | Python | 2018-03 | 4 | 5.869% | | 6 | PHP | 2018-03 | 7 | 4.010% | | 7 | Ruby | 2018-03 | 12 | 2.744% | | 8 | Java | 2018-03 | 1 | 14.941 | | 9 | Python | 2018-02 | 4 | 5.168% | | 10 | PHP | 2018-02 | 7 | 3.420% | | 11 | Ruby | 2018-02 | 10 | 2.534% | | 12 | Java | 2018-02 | 1 | 14.988% | +----+--------+---------+------+---------+
然后使用下面的语句创建一个左外连接
SELECT tbl_language.id,tbl_language.name,tbl_rank.rank FROM tbl_language LEFT OUTER JOIN tbl_rank on tbl_language.name = tbl_rank.name;
输出结果如下
id name rank ---------- ---------- ---------- 1 Python 4 1 Python 4 1 Python 4 2 PHP 6 2 PHP 7 2 PHP 7 3 Ruby 10 3 Ruby 11 3 Ruby 12 4 Kotlin