您的位置:首页 > 教程 > SQL server > SQL中的连接查询详解

SQL中的连接查询详解

2022-06-18 11:47:39 来源:易采站长站 作者:

SQL中的连接查询详解

Join 连接 (SQL>

SQL Join (连接) 是利用不同数据表之间字段的关连性来结合多数据表之检索。iDx站长之家-易采站长站-Easck.Com

SQL Join是结合多个数据表而组成一抽象的暂时性数据表以供数据查询,在原各数据表中之纪录及结构皆不会因此连接查询而改变。iDx站长之家-易采站长站-Easck.Com

这是一个客户数据表「customers」:iDx站长之家-易采站长站-Easck.Com

C_IdiDx站长之家-易采站长站-Easck.Com

NameiDx站长之家-易采站长站-Easck.Com

CityiDx站长之家-易采站长站-Easck.Com

AddressiDx站长之家-易采站长站-Easck.Com

PhoneiDx站长之家-易采站长站-Easck.Com

1iDx站长之家-易采站长站-Easck.Com

张一iDx站长之家-易采站长站-Easck.Com

台北市iDx站长之家-易采站长站-Easck.Com

XX路100号iDx站长之家-易采站长站-Easck.Com

02-12345678iDx站长之家-易采站长站-Easck.Com

2iDx站长之家-易采站长站-Easck.Com

王二iDx站长之家-易采站长站-Easck.Com

新竹县iDx站长之家-易采站长站-Easck.Com

YY路200号iDx站长之家-易采站长站-Easck.Com

03-12345678iDx站长之家-易采站长站-Easck.Com

3iDx站长之家-易采站长站-Easck.Com

李三iDx站长之家-易采站长站-Easck.Com

高雄县iDx站长之家-易采站长站-Easck.Com

ZZ路300号iDx站长之家-易采站长站-Easck.Com

07-12345678iDx站长之家-易采站长站-Easck.Com

而这是产品订单的数据表「orders」:iDx站长之家-易采站长站-Easck.Com

O_IdiDx站长之家-易采站长站-Easck.Com

OrderNoiDx站长之家-易采站长站-Easck.Com

C_IdiDx站长之家-易采站长站-Easck.Com

1iDx站长之家-易采站长站-Easck.Com

2572iDx站长之家-易采站长站-Easck.Com

3iDx站长之家-易采站长站-Easck.Com

2iDx站长之家-易采站长站-Easck.Com

7375iDx站长之家-易采站长站-Easck.Com

3iDx站长之家-易采站长站-Easck.Com

3iDx站长之家-易采站长站-Easck.Com

7520iDx站长之家-易采站长站-Easck.Com

1iDx站长之家-易采站长站-Easck.Com

4iDx站长之家-易采站长站-Easck.Com

1054iDx站长之家-易采站长站-Easck.Com

1iDx站长之家-易采站长站-Easck.Com

5iDx站长之家-易采站长站-Easck.Com

1257iDx站长之家-易采站长站-Easck.Com

5iDx站长之家-易采站长站-Easck.Com

其中,C_Id 是客户数据表中的主键 (Primary Key) 字段,我们怎么将这两张不同的数据表依相关字段来作个连接结合以便查询呢?这就是接下来的主题 Join!iDx站长之家-易采站长站-Easck.Com

SQL 的 Join 查询有哪几种类型?

    Inner>

    LEFT (OUTER) JOIN : 左外部连接iDx站长之家-易采站长站-Easck.Com

    RIGHT (OUTER) JOIN : 右外部连接iDx站长之家-易采站长站-Easck.Com

    FULL (OUTER) JOIN : 全部外部连接iDx站长之家-易采站长站-Easck.Com

    CROSS JOIN : 交叉连接iDx站长之家-易采站长站-Easck.Com

    NATURAL JOIN : 自然连接iDx站长之家-易采站长站-Easck.Com

    INNER>

    INNER JOIN (内部连接) 为等值连接,必需指定等值连接的条件,而查询结果只会返回符合连接条件的数据。iDx站长之家-易采站长站-Easck.Com

    INNER>
    SELECT table_column1, table_column2···
    FROM table_name1
    INNER JOIN table_name2
    ON table_name1.column_name=table_name2.column_name;

    iDx站长之家-易采站长站-Easck.Com

    SELECT table_column1, table_column2···
    FROM table_name1
    INNER JOIN table_name2
    USING (column_name);

    INNER>

    现在我们想列出所有客户的订单编号数据,我们可以作一个 INNER JOIN 查询:iDx站长之家-易采站长站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    INNER JOIN orders
    ON customers.C_Id=orders.C_Id;

    其中用点号连接之「XXX.YYY」表示XXX数据表中的YYY字段。iDx站长之家-易采站长站-Easck.Com

    查询结果如下:iDx站长之家-易采站长站-Easck.Com

    NameiDx站长之家-易采站长站-Easck.Com

    Order_NoiDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

    查询结果只会返回符合连接条件的数据!iDx站长之家-易采站长站-Easck.Com

    LEFT>

    LEFT JOIN 可以用来建立左外部连接,查询的 SQL 叙述句 LEFT JOIN 左侧数据表 (table_name1) 的所有记录都会加入到查询结果中,即使右侧数据表 (table_name2) 中的连接字段没有符合的值也一样。iDx站长之家-易采站长站-Easck.Com

    LEFT>
    SELECT table_column1, table_column2···
    FROM table_name1
    LEFT JOIN table_name2
    ON table_name1.column_name=table_name2.column_name;

    有些数据库的语法会是LEFT OUTER JOIN。iDx站长之家-易采站长站-Easck.Com

    LEFT>

    现在我们想查询所有客户与其订单状况的数据,我们可以作一个 LEFT JOIN 查询:iDx站长之家-易采站长站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    LEFT JOIN orders
    ON customers.C_Id=orders.C_Id;

    查询结果如下:iDx站长之家-易采站长站-Easck.Com

    NameiDx站长之家-易采站长站-Easck.Com

    Order_NoiDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

     

    李三iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    LEFT JOIN会返回左侧数据表中所有数据列,就算没有符合连接条件,而右侧数据表中如果没有匹配的数据值就会显示为「NULL」。iDx站长之家-易采站长站-Easck.Com

    RIGHT>

    相对于LEFT JOIN,RIGHT JOIN 可以用来建立右外部连接,查询的 SQL 叙述句 RIGHT JOIN 右侧数据表 (table_name2) 的所有记录都会加入到查询结果中,即使左侧数据表 (table_name2) 中的连接字段没有符合的值也一样。iDx站长之家-易采站长站-Easck.Com

    RIGHT>
    SELECT table_column1, table_column2···
    FROM table_name1
    RIGHT JOIN table_name2
    ON table_name1.column_name=table_name2.column_name;

    有些数据库的语法会是RIGHT OUTER JOIN。iDx站长之家-易采站长站-Easck.Com

    RIGHT>

    现在我们想查询所有订单与相应的客户之资料,我们可以作一个 RIGHT JOIN 查询:iDx站长之家-易采站长站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    RIGHT JOIN orders
    ON customers.C_Id=orders.C_Id;

    查询结果如下:iDx站长之家-易采站长站-Easck.Com

    NameiDx站长之家-易采站长站-Easck.Com

    Order_NoiDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

     

    1257iDx站长之家-易采站长站-Easck.Com

    RIGHT JOIN会返回右侧数据表中所有数据列,就算是没有符合连接条件,而左侧数据表中如果没有匹配的数据值就会显示为「NULL」。iDx站长之家-易采站长站-Easck.Com

    FULL>

    FULL JOIN 即为 LEFT JOIN 与 RIGHT JOIN 的联集,它会返回左右数据表中所有的纪录,不论是否符合连接条件。iDx站长之家-易采站长站-Easck.Com

    FULL>
    SELECT table_column1, table_column2···
    FROM table_name1
    FULL JOIN table_name2
    ON table_name1.column_name=table_name2.column_name;

    FULL>

    我们来作一个 FULL JOIN 查询:iDx站长之家-易采站长站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    FULL JOIN orders
    ON customers.C_Id=orders.C_Id;

    查询结果如下:iDx站长之家-易采站长站-Easck.Com

    NameiDx站长之家-易采站长站-Easck.Com

    Order_NoiDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

     

    1257iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

     

    MySQL数据库中没有FULL JOIN,但是您可以用UNION来模拟。iDx站长之家-易采站长站-Easck.Com

    CROSS>

    交叉连接为两个数据表间的笛卡儿乘积 (Cartesian product),两个数据表在结合时,不指定任何条件,即将两个数据表中所有的可能排列组合出来,以下例而言 CROSS JOIN 出来的结果资料列数为 3×5=15 笔,因此,当有WHERE、ON、USING条件时不建议使用。iDx站长之家-易采站长站-Easck.Com

    CROSS>
    SELECT table_column1, table_column2···
    FROM table_name1
    CROSS JOIN table_name2;

    iDx站长之家-易采站长站-Easck.Com

    SELECT table_column1, table_column2···
    FROM table_name1, table_name2;

    iDx站长之家-易采站长站-Easck.Com

    SELECT table_column1, table_column2···
    FROM table_name1
    JOIN table_name2;

    FULL>

    这是一个客户数据表「customers」:iDx站长之家-易采站长站-Easck.Com

    C_IdiDx站长之家-易采站长站-Easck.Com

    NameiDx站长之家-易采站长站-Easck.Com

    CityiDx站长之家-易采站长站-Easck.Com

    AddressiDx站长之家-易采站长站-Easck.Com

    PhoneiDx站长之家-易采站长站-Easck.Com

    1iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    台北市iDx站长之家-易采站长站-Easck.Com

    XX路100号iDx站长之家-易采站长站-Easck.Com

    02-12345678iDx站长之家-易采站长站-Easck.Com

    2iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

    新竹县iDx站长之家-易采站长站-Easck.Com

    YY路200号iDx站长之家-易采站长站-Easck.Com

    03-12345678iDx站长之家-易采站长站-Easck.Com

    3iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    高雄县iDx站长之家-易采站长站-Easck.Com

    ZZ路300号iDx站长之家-易采站长站-Easck.Com

    07-12345678iDx站长之家-易采站长站-Easck.Com

    而这是产品订单的数据表「orders」:iDx站长之家-易采站长站-Easck.Com

    O_IdiDx站长之家-易采站长站-Easck.Com

    Order_NoiDx站长之家-易采站长站-Easck.Com

    C_IdiDx站长之家-易采站长站-Easck.Com

    1iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    3iDx站长之家-易采站长站-Easck.Com

    2iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    3iDx站长之家-易采站长站-Easck.Com

    3iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    1iDx站长之家-易采站长站-Easck.Com

    4iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

    1iDx站长之家-易采站长站-Easck.Com

    5iDx站长之家-易采站长站-Easck.Com

    1257iDx站长之家-易采站长站-Easck.Com

    5iDx站长之家-易采站长站-Easck.Com

    我们来作一个 CROSS JOIN 查询:iDx站长之家-易采站长站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    CROSS JOIN orders;

    查询结果如下:iDx站长之家-易采站长站-Easck.Com

    NameiDx站长之家-易采站长站-Easck.Com

    Order_NoiDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    1257iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

    1257iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    1257iDx站长之家-易采站长站-Easck.Com

    NATURAL>

    自然连接有 NATURAL JOIN、NATURAL LEFT JOIN、NATURAL RIGHT JOIN,两个表格在进行 JOIN 时,加上 NATURAL 这个关键词之后,两数据表之间同名的字段会被自动结合在一起。iDx站长之家-易采站长站-Easck.Com

    NATURAL>
    SELECT table_column1, table_column2···
    FROM table_name1
    NATURAL JOIN table_name2;

    NATURAL>

    这是一个客户数据表「customers」:iDx站长之家-易采站长站-Easck.Com

    C_IdiDx站长之家-易采站长站-Easck.Com

    NameiDx站长之家-易采站长站-Easck.Com

    CityiDx站长之家-易采站长站-Easck.Com

    AddressiDx站长之家-易采站长站-Easck.Com

    PhoneiDx站长之家-易采站长站-Easck.Com

    1iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    台北市iDx站长之家-易采站长站-Easck.Com

    XX路100号iDx站长之家-易采站长站-Easck.Com

    02-12345678iDx站长之家-易采站长站-Easck.Com

    2iDx站长之家-易采站长站-Easck.Com

    王二iDx站长之家-易采站长站-Easck.Com

    新竹县iDx站长之家-易采站长站-Easck.Com

    YY路200号iDx站长之家-易采站长站-Easck.Com

    03-12345678iDx站长之家-易采站长站-Easck.Com

    3iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    高雄县iDx站长之家-易采站长站-Easck.Com

    ZZ路300号iDx站长之家-易采站长站-Easck.Com

    07-12345678iDx站长之家-易采站长站-Easck.Com

    而这是产品订单的数据表「orders」:iDx站长之家-易采站长站-Easck.Com

    O_IdiDx站长之家-易采站长站-Easck.Com

    Order_NoiDx站长之家-易采站长站-Easck.Com

    C_IdiDx站长之家-易采站长站-Easck.Com

    1iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    3iDx站长之家-易采站长站-Easck.Com

    2iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    3iDx站长之家-易采站长站-Easck.Com

    3iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    1iDx站长之家-易采站长站-Easck.Com

    4iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

    1iDx站长之家-易采站长站-Easck.Com

    5iDx站长之家-易采站长站-Easck.Com

    1257iDx站长之家-易采站长站-Easck.Com

    5iDx站长之家-易采站长站-Easck.Com

    现在我们想列出所有客户的订单编号数据,我们可以作一个 NATURAL JOIN 查询:iDx站长之家-易采站长站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    NATURAL JOIN orders;

    查询结果如下:iDx站长之家-易采站长站-Easck.Com

    NameiDx站长之家-易采站长站-Easck.Com

    Order_NoiDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    2572iDx站长之家-易采站长站-Easck.Com

    李三iDx站长之家-易采站长站-Easck.Com

    7375iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    7520iDx站长之家-易采站长站-Easck.Com

    张一iDx站长之家-易采站长站-Easck.Com

    1054iDx站长之家-易采站长站-Easck.Com

    注意到了吗?返回结果同等于下面这个INNER JOIN查询:iDx站长之家-易采站长站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    INNER JOIN orders
    ON customers.C_Id=orders.C_Id;

    到此这篇关于SQL连接查询的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持易采站长站。iDx站长之家-易采站长站-Easck.Com

    如有侵权,请联系QQ:279390809 电话:15144810328

相关文章

  • SQL Server 2019下载与安装教程(自定义安装)

    SQL Server 2019下载与安装教程(自定义安装)

    1.SQL Server2019安装包下载 1.1进入官网 SQL Server 2019 1.2下载安装包 1点击Continue 2.填写个人信息,再点击Continue 3.点击保存文件,就代表开始下载安装包 4.下载完成后,找到安装包并以管理员
    2020-04-28
  • 详解SQL Server表和索引存储结构

    详解SQL Server表和索引存储结构

    本文详细分析了SQL Server中表和索引结构存储的原理以及对于如何加快搜索速度和提高效率等方面做了详细的分析,以下是主要内容。 下图显示了表的存储组织,每张表有一个对应的对
    2020-07-04
  • SQL UNION 操作符

    SQL UNION 操作符

    SQL UNION 操作符 SQL UNION 操作符合并两个或多个 SELECT 语句的结果。 SQL UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意,UNION 内部的每个 SELECT 语句必须拥有相同
    2020-07-04
  • SQL Server的IP不能连接问题解决

    SQL Server的IP不能连接问题解决

    自己电脑上安装的SQLServer,用了一段时间后要联网发现连接不上,花了两三个小时才搞定,在此记录下免得再遇上 刚开始时用navicat连接 使用IP连接: 要是用IP连接的需要几个步骤:
    2020-07-04
  • SQL SERVER 分组求和sql语句

    SQL SERVER 分组求和sql语句

    需求:如下图所示 实现sql语句 SELECT A1,SUM(A2*A3) FROM A GROUP BY A1 大家可以自行测试一下,主要需要了解group by语句的用法 您可能感兴趣的文章: 分组后分组合计以及总计SQL语句(稍微整理
    2020-07-04
  • SQL Server 2012 sa用户登录错误18456的解决方法

    SQL Server 2012 sa用户登录错误18456的解决方法

    最近想研究下SQL SERVER2012 Enterprise版本的数据库,听说功能很强大。我是在win7上安装的,安装的过程很顺利,我在用“Windows 身份验证”时,一切OK,但是在用SA用户登录数据库的时候出
    2020-07-04
  • SQL Server中T-SQL 数据类型转换详解

    SQL Server中T-SQL 数据类型转换详解

    常用的转换函数是 cast 和 convert,用于把表达式得出的值的类型转换成另一个数据类型,如果转换失败,该函数抛出错误,导致整个事务回滚。在SQL Server 2012版本中,新增两个容错的转
    2020-07-04
  • SQL Server数据库设置自动备份策略的完整步骤

    SQL Server数据库设置自动备份策略的完整步骤

    先了解一下:为何要做备份? 数据备份是容灾的基础,是指为防止系统出现操作失误或系统故障导致数据丢失,而将全部或部分数据集合从应用主机的硬盘或阵列复制到其它的存储介质
    2020-07-04