其实联结很简单,简单来说就是对两个表求笛卡尔积。所谓笛卡尔积就是求两表所有行的组合,假设左表有 n 行记录、右表有 m 行记录,则两表的笛卡尔积有 nm 行记录。需要注意的是,和计算字段一样,所有联结数据表都不是物理的。
1
SELECT*FROM MovieStar;
1
SELECT*FROM StarsIn;
1
SELECT*FROM MovieStar, StarsIn; -- 笛卡尔积
1 2 3 4 5 6 7 8 9 10 11
+----------------+-------------+--------+------------+ | name | address | gender | birthDate | +----------------+-------------+--------+------------+ | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | | Debra Winger | A way | F | 1978-05-06 | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Jack Nicholson | X path | M | 1949-05-05 | | Jane Fonda | Turner Av. | F | 1977-07-07 | +----------------+-------------+--------+------------+ 6 rows in set (0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12
+---------------------+-----------+----------------+ | movieTitle | movieYear | starName | +---------------------+-----------+----------------+ | Empire Strikes Back | 1980 | Harrison Ford | | Star Wars | 1977 | Carrie Fisher | | Star Wars | 1977 | Harrison Ford | | Star Wars | 1977 | Mark Hamill | | Terms of Endearment | 1983 | Debra Winger | | Terms of Endearment | 1983 | Jack Nicholson | | The Usual Suspects | 1995 | Kevin Spacey | +---------------------+-----------+----------------+ 7 rows in set (0.00 sec)
+----------------+-------------+--------+------------+---------------------+-----------+----------------+ | name | address | gender | birthDate | movieTitle | movieYear | starName | +----------------+-------------+--------+------------+---------------------+-----------+----------------+ | Jane Fonda | Turner Av. | F | 1977-07-07 | Empire Strikes Back | 1980 | Harrison Ford | | Jack Nicholson | X path | M | 1949-05-05 | Empire Strikes Back | 1980 | Harrison Ford | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | Empire Strikes Back | 1980 | Harrison Ford | | Debra Winger | A way | F | 1978-05-06 | Empire Strikes Back | 1980 | Harrison Ford | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | Empire Strikes Back | 1980 | Harrison Ford | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | Empire Strikes Back | 1980 | Harrison Ford | | Jane Fonda | Turner Av. | F | 1977-07-07 | Star Wars | 1977 | Carrie Fisher | | Jack Nicholson | X path | M | 1949-05-05 | Star Wars | 1977 | Carrie Fisher | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | Star Wars | 1977 | Carrie Fisher | | Debra Winger | A way | F | 1978-05-06 | Star Wars | 1977 | Carrie Fisher | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | Star Wars | 1977 | Carrie Fisher | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | Star Wars | 1977 | Carrie Fisher | | Jane Fonda | Turner Av. | F | 1977-07-07 | Star Wars | 1977 | Harrison Ford | | Jack Nicholson | X path | M | 1949-05-05 | Star Wars | 1977 | Harrison Ford | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | Star Wars | 1977 | Harrison Ford | | Debra Winger | A way | F | 1978-05-06 | Star Wars | 1977 | Harrison Ford | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | Star Wars | 1977 | Harrison Ford | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | Star Wars | 1977 | Harrison Ford | | Jane Fonda | Turner Av. | F | 1977-07-07 | Star Wars | 1977 | Mark Hamill | | Jack Nicholson | X path | M | 1949-05-05 | Star Wars | 1977 | Mark Hamill | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | Star Wars | 1977 | Mark Hamill | | Debra Winger | A way | F | 1978-05-06 | Star Wars | 1977 | Mark Hamill | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | Star Wars | 1977 | Mark Hamill | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | Star Wars | 1977 | Mark Hamill | | Jane Fonda | Turner Av. | F | 1977-07-07 | Terms of Endearment | 1983 | Debra Winger | | Jack Nicholson | X path | M | 1949-05-05 | Terms of Endearment | 1983 | Debra Winger | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | Terms of Endearment | 1983 | Debra Winger | | Debra Winger | A way | F | 1978-05-06 | Terms of Endearment | 1983 | Debra Winger | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | Terms of Endearment | 1983 | Debra Winger | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | Terms of Endearment | 1983 | Debra Winger | | Jane Fonda | Turner Av. | F | 1977-07-07 | Terms of Endearment | 1983 | Jack Nicholson | | Jack Nicholson | X path | M | 1949-05-05 | Terms of Endearment | 1983 | Jack Nicholson | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | Terms of Endearment | 1983 | Jack Nicholson | | Debra Winger | A way | F | 1978-05-06 | Terms of Endearment | 1983 | Jack Nicholson | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | Terms of Endearment | 1983 | Jack Nicholson | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | Terms of Endearment | 1983 | Jack Nicholson | | Jane Fonda | Turner Av. | F | 1977-07-07 | The Usual Suspects | 1995 | Kevin Spacey | | Jack Nicholson | X path | M | 1949-05-05 | The Usual Suspects | 1995 | Kevin Spacey | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | The Usual Suspects | 1995 | Kevin Spacey | | Debra Winger | A way | F | 1978-05-06 | The Usual Suspects | 1995 | Kevin Spacey | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | The Usual Suspects | 1995 | Kevin Spacey | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | The Usual Suspects | 1995 | Kevin Spacey | +----------------+-------------+--------+------------+---------------------+-----------+----------------+ 42 rows in set (0.00 sec)
但是我们也看到了,在上面这个 42 行记录的表并没有什么实际意义,因此联结通常与筛选混用。
1 2 3 4 5 6 7 8 9 10
SELECT movieTitle, movieYear, M.* FROM StarsIn AS S, MovieStar AS M WHERE S.starName = M.name; -- 筛选所有明星出演的电影, 并显示明星的详细信息
1 2 3 4 5 6 7 8
+---------------------+-----------+----------------+-------------+--------+------------+ | movieTitle | movieYear | name | address | gender | birthDate | +---------------------+-----------+----------------+-------------+--------+------------+ | Empire Strikes Back | 1980 | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Star Wars | 1977 | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Terms of Endearment | 1983 | Debra Winger | A way | F | 1978-05-06 | | Terms of Endearment | 1983 | Jack Nicholson | X path | M | 1949-05-05 | +---------------------+-----------+----------------+-------------+--------+------------+
内联结与外联结
其实上面我们介绍的这种使用 WHERE 的联结方式就是内联结。但 SQL 提供了一套联结的专用语法,内联结的关键字为 INNER JOIN。
此外还有多种外联结,包括:LEFT JOIN (左联结) / RIGHT JOIN (右联结) / FULL JOIN (全联结)。
1 2 3
SELECT {字段...} FROM {主表} LEFT [OUTER] JOIN {副表} ON {条件表达式} -- 左联结 SELECT {字段...} FROM {主表} RIGHT [OUTER] JOIN {副表} ON {条件表达式} -- 右联结 SELECT {字段...} FROM {主表} FULL [OUTER] JOIN {副表} ON {条件表达式} -- 全联结
SELECT movieTitle, movieYear, M.* FROM StarsIn AS S INNERJOIN MovieStar AS M ON S.starName = M.name; -- 内联结示例
1 2 3 4 5 6 7 8 9
SELECT movieTitle, movieYear, M.* FROM StarsIn AS S LEFTJOIN MovieStar AS M ON S.starName = M.name; -- 外联结示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SELECT title, year, length, movieType, studioName, address AS studioAddress, starName FROM Movies AS M INNERJOIN Studio AS S ON studioName = name LEFTJOIN StarsIn ON title = movieTitle; -- 联结的嵌套使用
1 2 3 4 5 6 7 8
+---------------------+-----------+----------------+-------------+--------+------------+ | movieTitle | movieYear | name | address | gender | birthDate | +---------------------+-----------+----------------+-------------+--------+------------+ | Empire Strikes Back | 1980 | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Star Wars | 1977 | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Terms of Endearment | 1983 | Debra Winger | A way | F | 1978-05-06 | | Terms of Endearment | 1983 | Jack Nicholson | X path | M | 1949-05-05 | +---------------------+-----------+----------------+-------------+--------+------------+
1 2 3 4 5 6 7 8 9 10 11
+---------------------+-----------+----------------+-------------+--------+------------+ | movieTitle | movieYear | name | address | gender | birthDate | +---------------------+-----------+----------------+-------------+--------+------------+ | Empire Strikes Back | 1980 | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Star Wars | 1977 | NULL | NULL | NULL | NULL | | Star Wars | 1977 | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Star Wars | 1977 | NULL | NULL | NULL | NULL | | Terms of Endearment | 1983 | Debra Winger | A way | F | 1978-05-06 | | Terms of Endearment | 1983 | Jack Nicholson | X path | M | 1949-05-05 | | The Usual Suspects | 1995 | NULL | NULL | NULL | NULL | +---------------------+-----------+----------------+-------------+--------+------------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
+--------------------------+------+--------+-----------+----------------+--------------------------+----------------+ | title | year | length | movieType | studioName | studioAddress | starName | +--------------------------+------+--------+-----------+----------------+--------------------------+----------------+ | Empire Strikes Back | 1980 | 111 | drama | Fox | Fox Boulevard | Harrison Ford | | My people,My country | 2019 | 158 | feature | huaxia | Boulevard | NULL | | Gone With the Wind | 1938 | 238 | drama | MGM | MGM Boulevard | NULL | | Logan's run | 1977 | 120 | drama | MGM | MGM Boulevard | NULL | | Pretty Woman | 1990 | 119 | drama | Disney | Disney Boulevard | NULL | | Star Trek | 1979 | NULL | sciFic | Paramount | Paramount Boulevard | NULL | | Star Trek: Nemesis | 2002 | 116 | sciFic | Paramount | Paramount Boulevard | NULL | | Star Wars | 1977 | 124 | sciFic | Fox | Fox Boulevard | Mark Hamill | | Star Wars | 1977 | 124 | sciFic | Fox | Fox Boulevard | Harrison Ford | | Star Wars | 1977 | 124 | sciFic | Fox | Fox Boulevard | Carrie Fisher | | Terms of Endearment | 1983 | 132 | drama | MGM | MGM Boulevard | Jack Nicholson | | Terms of Endearment | 1983 | 132 | drama | MGM | MGM Boulevard | Debra Winger | | The Man Who Wasn't There | 2001 | 116 | comedy | USA Entertainm | USA Entertainm Boulevard | NULL | | The Usual Suspects | 1995 | 106 | drama | MGM | MGM Boulevard | Kevin Spacey | +--------------------------+------+--------+-----------+----------------+--------------------------+----------------+
另外多提一嘴,在 SQLite 中没有 RIGHT JOIN,此时只需简单替换主表和副表的位置然后使用 LEFT JOIN 即可。
自然联结的关键字为 NATURAL JOIN,并且不能使用 ON 进行筛选。因为自然联结的含义是:自动推导筛选条件 。自然联结是一种内联结。自然联结会将两表中相同的字段作为条件联结,例如 A 表和 B 表中均有字段 name 和 address,则自动推导的筛选条件为 A.name=B.name AND A.address=B.address,另外显示时自动隐去 B 表中的相同字段。
1
SELECT*FROM MovieStar NATURALJOIN MovieExec;
1 2 3 4 5
+------------+------------+--------+------------+------+-----------+ | name | address | gender | birthDate | cert | netWorth | +------------+------------+--------+------------+------+-----------+ | Jane Fonda | Turner Av. | F | 1977-07-07 | 567 | 200000000 | +------------+------------+--------+------------+------+-----------+
SELECT M.title, M.year, M.length, M.movieType, M.studioName, ( SELECT S.address FROM Studio AS S WHERE M.studioName = S.name ) AS studioAddress FROM Movies AS M WHERE M.studioName IN ( SELECT S.name FROM Studio AS S ); -- 子查询解决方案
1 2 3 4 5 6 7 8 9 10 11 12 13
SELECT M.title, M.year, M.length, M.movieType, M.studioName, S.address AS studioAddress FROM Movies AS M, Studio AS S WHERE M.studioName = S.name; -- 笛卡尔积解决方案(也是联结, 但没用 JOIN)
1 2 3 4 5 6 7 8 9 10 11 12
SELECT M.title, M.year, M.length, M.movieType, M.studioName, S.address AS studioAddress FROM Movies AS M INNERJOIN Studio AS S ON M.studioName = S.name; -- 联结解决方案
三个输出是一样的:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
+--------------------------+------+--------+-----------+----------------+--------------------------+ | title | year | length | movieType | studioName | studioAddress | +--------------------------+------+--------+-----------+----------------+--------------------------+ | Empire Strikes Back | 1980 | 111 | drama | Fox | Fox Boulevard | | My people,My country | 2019 | 158 | feature | huaxia | Boulevard | | Gone With the Wind | 1938 | 238 | drama | MGM | MGM Boulevard | | Logan's run | 1977 | 120 | drama | MGM | MGM Boulevard | | Pretty Woman | 1990 | 119 | drama | Disney | Disney Boulevard | | Star Trek | 1979 | NULL | sciFic | Paramount | Paramount Boulevard | | Star Trek: Nemesis | 2002 | 116 | sciFic | Paramount | Paramount Boulevard | | Star Wars | 1977 | 124 | sciFic | Fox | Fox Boulevard | | Terms of Endearment | 1983 | 132 | drama | MGM | MGM Boulevard | | The Man Who Wasn't There | 2001 | 116 | comedy | USA Entertainm | USA Entertainm Boulevard | | The Usual Suspects | 1995 | 106 | drama | MGM | MGM Boulevard | +--------------------------+------+--------+-----------+----------------+--------------------------+