MySQL 使用篇连接查询

「爱情、让人受尽委屈。」 2022-11-30 15:48 188阅读 0赞

### 连接查询 笛卡尔积 ###

现有`t1` `t2` 两张表

mysql> SELECT * FROM t1;
    +------+------+
    | m1   | n1   |
    +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    +------+------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT * FROM t2;
    +------+------+
    | m2   | n2   |
    +------+------+
    |    2 | b    |
    |    3 | c    |
    |    4 | d    |
    +------+------+
    3 rows in set (0.00 sec)
    
    mysql>连接

连接查询，如果不做限制，就是 `笛卡尔积`组合

笛卡尔积就是两两组合。

比如（1,2,3）（a,b,c）

组合后就是 1a 1b 1c 2a 2b 2c 3a 3b 3c

SELECT * FROM t1, t2;
    
    +------+------+------+------+
    | m1   | n1   | m2   | n2   |
    +------+------+------+------+
    |    1 | a    |    2 | b    |
    |    2 | b    |    2 | b    |
    |    3 | c    |    2 | b    |
    |    1 | a    |    3 | c    |
    |    2 | b    |    3 | c    |
    |    3 | c    |    3 | c    |
    |    1 | a    |    4 | d    |
    |    2 | b    |    4 | d    |
    |    3 | c    |    4 | d    |
    +------+------+------+------+
    
    # 下面的方式等价
    SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;
    SELECT m1, n1, m2, n2 FROM t1, t2;
    SELECT t1.*, t2.* FROM t1, t2;

### 外连接与内连接 ###

# 左（外）连接的语法
    SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];
    
    # 右（外）连接的语法
    SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];
    
    # 内连接的语法
    SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];
    # mysql中，以下写法等价
    SELECT * FROM t1 JOIN t2;
    SELECT * FROM t1 INNER JOIN t2;
    SELECT * FROM t1 CROSS JOIN t2;

**例：**

mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
    +------+------+------+------+
    | m1   | n1   | m2   | n2   |
    +------+------+------+------+
    |    2 | b    |    2 | b    |
    |    3 | c    |    3 | c    |
    +------+------+------+------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
    +------+------+------+------+
    | m1   | n1   | m2   | n2   |
    +------+------+------+------+
    |    2 | b    |    2 | b    |
    |    3 | c    |    3 | c    |
    |    1 | a    | NULL | NULL |
    +------+------+------+------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2;
    +------+------+------+------+
    | m1   | n1   | m2   | n2   |
    +------+------+------+------+
    |    2 | b    |    2 | b    |
    |    3 | c    |    3 | c    |
    | NULL | NULL |    4 | d    |
    +------+------+------+------+
    3 rows in set (0.00 sec)