mysql中join内外连接查询例子

join关键字

在MySQL中，JOIN 是一种用于将两个或多个表中的行联合起来的操作。
连接（join）就是将一张表中的行按照某个条件（连接条件）与另一张表中的行连接起来形成一个新行的过程。这个过程可以用来合并相关联的数据，以便于进行查询、分析或其他操作。

概要

当根据连接条件执行连接查询时，可以将结果分为三类：

1. 内连接（Inner Join）：^常用
   内连接返回两个表中满足连接条件的行，即同时在两个表中存在匹配的行。如果某行在其中一个表中没有匹配的行，则不会包含在结果中。

内连接SQL句式：

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

2. 外连接（Outer Join）：^常用
   • 左外连接（Left Outer Join）返回左表中的所有行，以及右表中满足连接条件的行，如果右表中没有匹配的行，则返回NULL值。用LEFT JOIN
   • 右外连接（Right Outer Join）返回右表中的所有行，以及左表中满足连接条件的行，如果左表中没有匹配的行，则返回 NULL 值。用RIGHT JOIN
   • 全外连接（Full Outer Join）返回两个表中的所有行，如果某个表中没有匹配的行，则返回 NULL 值。用FULL JOIN

左外连接SQL句式：

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

3. 交叉连接（Cross Join）：^不常用
   • 交叉连接返回两个表中的所有可能的组合，它不需要任何连接条件，它只是简单地将第一个表中的每一行与第二个表中的每一行进行组合。
   • 交叉连接可以通过省略连接条件或使用 CROSS JOIN 关键字来实现。

交叉连接SQL句式：

SELECT *
FROM table1
CROSS JOIN table2;

举例

准备表，数据

-- 创建用户表
CREATE TABLE users
(
    user_id  INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email    VARCHAR(50)
);

-- 创建订单表
CREATE TABLE orders
(
    order_id     INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT,
    product_name VARCHAR(100),
    amount       DECIMAL(10, 2),
    order_date   DATE,
    FOREIGN KEY (user_id) REFERENCES users (user_id)
);

-- 添加示例数据到用户表
INSERT INTO users (username, email)
VALUES ('alice', 'alice@qq.com'),
       ('bob', 'bob@qq.com'),
       ('charlie', 'charlie@qq.com'),
       ('bob', 'bob@qq.com');

-- 添加示例数据到订单表
INSERT INTO orders (user_id, product_name, amount, order_date)
VALUES (1, 'product a', 70.00, '2024-04-25'),
       (2, 'product b', 60.00, '2024-04-25'),
       (1, 'product c', 20.00, '2024-04-26'),
       (3, 'product d', 40.00, '2024-04-26'),
       (NULL, 'Product E', 60.00, '2024-04-27');

两张表如下

第一种：内连接查询（INNER JOIN）：返回同时在用户表和订单表中存在的行

# 写法1
SELECT users.username, orders.product_name, orders.amount, orders.order_date
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;

# 写法2
SELECT users.username, orders.product_name, orders.amount, orders.order_date
FROM users,
     orders
WHERE users.user_id = orders.user_id;

第二种：左外连接查询（LEFT JOIN）：返回左表中的所有行，以及右表中匹配的行，如果右表中没有匹配的行，则显示 NULL 值。

# 写法1
SELECT users.username, orders.product_name, orders.amount, orders.order_date
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;

# 写法2 后面介绍 using  on 区别
SELECT users.username, orders.product_name, orders.amount, orders.order_date
FROM users
LEFT JOIN orders  using(user_id);

第三种：右外连接查询（RIGHT JOIN）：返回右表中的所有行，以及左表中匹配的行，如果左表中没有匹配的行，则显示 NULL 值。

SELECT users.username, orders.product_name, orders.amount, orders.order_date
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;

using 与 on 区别

USING：

• USING 关键字用于在两个表中存在相同列名的情况下简化连接条件的书写。
• 使用 USING 时，只需要指定相同列名，而不需要重复列出列名，并且连接条件中不需要指定表名。
• USING 只能指定一个列作为连接条件，且连接列的名称在两个表中必须相同。

ON：

• ON 关键字用于在连接操作中指定任意的连接条件，不限于相同列名。
• 使用 ON 时，可以指定任意的连接条件，通常使用在连接条件不是简单的列名相等的情况下。
• ON 可以指定多个连接条件，连接条件的灵活性更高。

谢谢点赞