概念 INNER JOIN（內連接）：獲取兩個表中欄位匹配關係的記錄。也就是只會返回共有的內容。 LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。 RIGHT JOIN（右連接）： 獲取右表所有記錄，即使左表沒有對應匹配的記錄。 示例 先在資料庫中建立兩張表student ...
概念
- INNER JOIN（內連接）：獲取兩個表中欄位匹配關係的記錄。也就是只會返回共有的內容。
- LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。
- RIGHT JOIN（右連接）： 獲取右表所有記錄，即使左表沒有對應匹配的記錄。
示例
-
先在資料庫中建立兩張表student和score，具體內容如下：
【student】
mysql> select * from student; -------------- select * from student -------------- +----+---------------------+------+-------+------------+-----------+ | id | name | sex | birth | department | address | +----+---------------------+------+-------+------------+-----------+ | 1 | RooneyMara | F | 1985 | Psychology | American | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | | 3 | EllenPage | F | 1987 | Music | Canada | | 4 | TomHolland | M | 1996 | CS | England | | 5 | ScarlettJohansson | F | 1984 | Music | American | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | | 7 | EvaGreen | F | 1980 | Math | France | +----+---------------------+------+-------+------------+-----------+ 7 rows in set (0.00 sec)
【score】
mysql> select * from score; -------------- select * from score -------------- +----+--------+------------+-------+ | id | stu_id | c_name | grade | +----+--------+------------+-------+ | 1 | 1 | Psychology | 98 | | 2 | 1 | Music | 80 | | 3 | 2 | Psychology | 65 | | 4 | 2 | CS | 88 | | 5 | 3 | CS | 95 | | 6 | 4 | Psychology | 70 | | 7 | 4 | Music | 92 | | 8 | 5 | Music | 94 | | 9 | 6 | Psychology | 90 | | 10 | 6 | CS | 85 | | 11 | 8 | Music | 91 | +----+--------+------------+-------+ 11 rows in set (0.00 sec)
-
內連接
查詢student表中的所有個人信息及score表中的c_name,grade
mysql> select a.*,c_name,grade from student a join score b on a.id=b.stu_id; -------------- select a.*,c_name,grade from student a join score b on a.id=b.stu_id -------------- +----+---------------------+------+-------+------------+-----------+------------+-------+ | id | name | sex | birth | department | address | c_name | grade | +----+---------------------+------+-------+------------+-----------+------------+-------+ | 1 | RooneyMara | F | 1985 | Psychology | American | Psychology | 98 | | 1 | RooneyMara | F | 1985 | Psychology | American | Music | 80 | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | Psychology | 65 | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | CS | 88 | | 3 | EllenPage | F | 1987 | Music | Canada | CS | 95 | | 4 | TomHolland | M | 1996 | CS | England | Psychology | 70 | | 4 | TomHolland | M | 1996 | CS | England | Music | 92 | | 5 | ScarlettJohansson | F | 1984 | Music | American | Music | 94 | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | Psychology | 90 | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | CS | 85 | +----+---------------------+------+-------+------------+-----------+------------+-------+ 10 rows in set (0.00 sec)
以上語句等價於：
mysql> select a.*,c_name,grade from student a,score b where a.id=b.stu_id; -------------- select a.*,c_name,grade from student a,score b where a.id=b.stu_id -------------- +----+---------------------+------+-------+------------+-----------+------------+-------+ | id | name | sex | birth | department | address | c_name | grade | +----+---------------------+------+-------+------------+-----------+------------+-------+ | 1 | RooneyMara | F | 1985 | Psychology | American | Psychology | 98 | | 1 | RooneyMara | F | 1985 | Psychology | American | Music | 80 | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | Psychology | 65 | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | CS | 88 | | 3 | EllenPage | F | 1987 | Music | Canada | CS | 95 | | 4 | TomHolland | M | 1996 | CS | England | Psychology | 70 | | 4 | TomHolland | M | 1996 | CS | England | Music | 92 | | 5 | ScarlettJohansson | F | 1984 | Music | American | Music | 94 | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | Psychology | 90 | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | CS | 85 | +----+---------------------+------+-------+------------+-----------+------------+-------+ 10 rows in set (0.00 sec)
-
左連接
student表中id為7的數據，在score中沒有對應的內容。所以最後一條查詢結果c_name,grade對應內容為null。
mysql> select a.*,c_name,grade from student a left join score b on a.id=b.stu_id; -------------- select a.*,c_name,grade from student a left join score b on a.id=b.stu_id -------------- +----+---------------------+------+-------+------------+-----------+------------+-------+ | id | name | sex | birth | department | address | c_name | grade | +----+---------------------+------+-------+------------+-----------+------------+-------+ | 1 | RooneyMara | F | 1985 | Psychology | American | Psychology | 98 | | 1 | RooneyMara | F | 1985 | Psychology | American | Music | 80 | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | Psychology | 65 | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | CS | 88 | | 3 | EllenPage | F | 1987 | Music | Canada | CS | 95 | | 4 | TomHolland | M | 1996 | CS | England | Psychology | 70 | | 4 | TomHolland | M | 1996 | CS | England | Music | 92 | | 5 | ScarlettJohansson | F | 1984 | Music | American | Music | 94 | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | Psychology | 90 | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | CS | 85 | | 7 | EvaGreen | F | 1980 | Math | France | NULL | NULL | +----+---------------------+------+-------+------------+-----------+------------+-------+ 11 rows in set (0.00 sec)
-
右連接
score表中id為11的數據，在student中沒有對應的內容，所以最後一條查詢結果id,name,sex等對應內容為null。
mysql> select a.*,c_name,grade from student a right join score b on a.id=b.stu_id; -------------- select a.*,c_name,grade from student a right join score b on a.id=b.stu_id -------------- +------+---------------------+------+-------+------------+-----------+------------+-------+ | id | name | sex | birth | department | address | c_name | grade | +------+---------------------+------+-------+------------+-----------+------------+-------+ | 1 | RooneyMara | F | 1985 | Psychology | American | Psychology | 98 | | 1 | RooneyMara | F | 1985 | Psychology | American | Music | 80 | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | Psychology | 65 | | 2 | ChrisHemsworth | M | 1983 | CS | Australia | CS | 88 | | 3 | EllenPage | F | 1987 | Music | Canada | CS | 95 | | 4 | TomHolland | M | 1996 | CS | England | Psychology | 70 | | 4 | TomHolland | M | 1996 | CS | England | Music | 92 | | 5 | ScarlettJohansson | F | 1984 | Music | American | Music | 94 | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | Psychology | 90 | | 6 | BenedictCumberbatch | M | 1976 | Psychology | England | CS | 85 | | NULL | NULL | NULL | NULL | NULL | NULL | Music | 91 | +------+---------------------+------+-------+------------+-----------+------------+-------+ 11 rows in set (0.00 sec)
