SQL连接表

什么是连接表？

多表查询原理：将多个表通过笛卡尔积形成一个虚表，再根据查询条件筛选符合条件的数据。
在关系数据库中，数据分布在多个逻辑表中。要获得完整有意义的数据集，需要使用连接来查询这些表
中的数据。 SQL Server支持多种
- 连接包括
  - INNER JOIN:内连接,关键字在表中存在至少一个匹配时返回行。
  - left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
  - right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
  - inner join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
  - full join : 外连接，返回两个表中的行：left join + right join。
  - cross join : 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
  - GROUP BY:全外连接，子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前

每种连接类型指定SQL Server如何使用一个表中的数据来选择另一个表中的行

1. SQL Server内连接

内连接是SQL Server中最常用的连接之一。 内部联接子句用于查询来自两个或多个相关表的数据。
SQL Server INNER JOIN 子句的语法：
在此语法中，从 T1 和 T2 表中查询检索数据：
- 首先，在 FROM 子句中指定主表( T1 )
- 其次，在 INNER JOIN 子句和连接谓词中指定第二个表( T2 )。只有连接谓词计算为 TRUE 的行才包
  含在结果集中。

**INNER JOIN **子句将表 T1 的每一行与表 T2 的行进行比较，以查找满足连接谓词的所有行对。如果连接
谓词的计算结果为 TRUE ，则匹配
的 T1 和 T2 行的列值将合并为一个新行并包含在结果集中。

下图说明了两个结果集的内联接的结果：
使用两个 INNER JOIN 子句来查询三个表中的数据：

图中左右两边的表是有关联的，中间黄色的交互区就是他们两个关联表中关联的数据。
使用INNER JOIN可以在两个有关联数据的表中把关联的数据查询出来

使用两个 INNER JOIN 子句来查询三个表中的数据：
INNER JOIN 关键字在表中存在至少一个匹配时返回行。

SELECT
    --在下面吗三张表中需要查询的数据
    product_name,
    category_name,
    brand_name, 
    list_price
FROM
    数据表(1) p    -- p是数据表的引用变量，可以通过p.出数据表格(1)中的列名 
--使用INNER JOIN通过数据表(1)category_id值查询出对应在数据表(2)的关联数据
INNER JOIN 数据表(2) c ON c数据表(2).category_id = p数据表(1).category_id 
--使用INNER JOIN通过数据表(1)中brand_id值查询出对应在数据表(3)的关联数据
INNER JOIN 数据表(3) b ON b数据表(3).brand_id = p数据表(1).brand_id 
ORDER BY     --查询结果升序排序
    product_name DESC;

2. SQL Server左连接

LEFT JOIN 子句用于查询来自多个表的数据。它返回左表中的所有行和右表中的匹配行。
如果在右表中找不到匹配的行，则使用 NULL 代替显示。

以下图说明了两个结果集的左连接结果：

以下说明如何使用 LEFT JOIN 子句来连接两个表 student(学生表) 和 sc(成绩表) ：

下面的小案例左表(学生表)通过某列名在右表(成绩表)中查询出在相同列名中的一行数据

SELECT
    s.sno,--学生表中的学生学号        
    c.cno,--成绩表中的课程号
    c.score--成绩表中学生的在此课程中的成绩
FROM 
    student s             
LEFT JOIN sc c ON--使用LEFT JOIN获取sc(成绩表)
    c.sno=s.sno; --通过student(学生ID)等于成绩表中(学生ID)选择到学生表在成绩表中的数据

以下是上面左连接查询语句的查询结果:*

图中为空的格子是在右表(成绩表)中没有该学生学号下的考试课程与成绩，所以默认返回了一个NULL值

对于学生表中的每一行，查询将其与成绩表中的所有行进行比较。如果一对行导致连接谓词计算为
TRUE ，则将组合这些行中的列值以形成新行，然后将其包含在结果集中。

如果左表( T1 )中的行没有与来自 T2 表的任何匹配行，则查询将左表中的行的列值与来自右表的每个列
值的 NULL 组合。

3. ON与WHERE子句

关键字 on

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用 left jion 时，on 和 where 条件的区别如下：

1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

两条SQL语句:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

其实以上结果的关键原因就是left join、right join、full join的特殊性，不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。 而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

4. SQL Server右连接

RIGHT JOIN 子句组合来自两个或多个表的数据。

RIGHT JOIN 开始从**右表中选择数据并与左表中的行
匹配。 RIGHT JOIN 返回一个结果集，该结果集包含右表中的所有行，无论是否具有左表中的匹配行。
如果右表中的行没有来自右表的任何匹配行，则结果集中右表的列将使用 NULL 值。

以下是 RIGHT JOIN 的语法：

SELECT
    select_list
FROM
    T1
RIGHT JOIN T2 ON join_predicate; 
SQL

在此语法中， T1 是左表， T2 是右表。

请注意， RIGHT JOIN 和 RIGHT OUTER JOIN 是相同的。 OUTER 关键字是可选的。
下图说明了 RIGHT JOIN 操作

橙色部分表示返回的结果集。
SQL Server RIGHT JOIN示例
以下语句返回 production.products 表中的产品名称和 sales.order_items 所有 order_id ：

SELECT
    product_name,
    order_id 
FROM
    sales.order_items o 
RIGHT JOIN 
    production.products p ON o.product_id = p.product_id 
ORDER BY 
    order_id;

5. SQL Server 交叉连接

CROSS JOIN 连接两个或多个不相关的表。
以下是两个表的SQL Server CROSS JOIN 的语法：

SELECT 
    select_list 
FROM 
    T1 
     CROSS JOIN T2;

CROSS JOIN 将第一个表(T1)中的每一行与第二个表(T2)中的每一行连接起来。换句话说，交叉连接返回
两个表中行的笛卡尔积。与INNER JOIN或LEFT JOIN不同，交叉连接不会在连接的表之间建立关系。

假设 T1 表包含三行： 1 , 2 和 3 ， T2 表包含三行： A ， B 和 C 。 CROSS JOIN 从第一个表(T1)获取一行，然后为第二个表(T2)中的每一行创建一个新行。然后它对第一个
表(T1)中的下一行执行相同操作，依此类推。

在此图中， CROSS JOIN 总共创建了 9 行。通常，如果第一个表有 n 行，第二个表有 m 行，则交叉连接
将产生 n x m 行。

6. SQL Server自连接

自联接用于将表连接到自身(同一个表)。它对于查询分层数据或比较同一个表中的行很有用。
自联接使用内连接或左连接子句。由于使用自联接的查询引用同一个表，因此表别名用于为查询中的表
分配不同的名称

请注意，如果在不使用表别名的情况下在查询中多次引用同一个表，则会出现错误。

SELECT
    t1.sno,t2.sno
FROM
    sc t1,sc t2

上面查询语句中两次引用表 T 。表别名 t1 和 t2 用于为 T 表分配不同的名称。

staffs 表存储员工信息，如身份证，名字，姓氏和电子邮件。它还有一个名为 manager_id 的列，用
于指定直接管理者。例如，员工 Mireya 向管理员者 Fabiola 汇报工作，因为 Mireya 的 manager_id
列中的值是 Fabiola 。 Fabiola 没有经理，因为它的 manager_id 列是一个 NULL 值。
要获取工作汇报关系，请使用自联接，如以下查询中所示：

SELECT
    e.first_name + ' ' + e.last_name employee, 
    m.first_name + ' ' + m.last_name manager 
FROM
    sales.staffs e 
    INNER JOIN sales.staffs m ON m.staff_id = e.manager_id 
ORDER BY 
    manager;

在这个例子中，两次引用了 staffs 表：一个是员工的 e ，另一个是管理者的 m 。
连接谓词使用 e.manager_id 和 m.staff_id 列中的值匹配 employee 和 manager 关系。
由于 INNER JOIN 效应， employee 列没有 Fabiola Jackson 。如果用 LEFT JOIN 子句替换 INNER JOIN 子句，将获得在 employee 列中包含 Fabiola Jackson 的结果集

7. SQL Server全外链接?

FULL OUTER JOIN当左表或右表中存在匹配项时，该命令将返回所有行。
以下SQL语句选择所有客户和所有订单：

SELECT 
    Customers.CustomerName, 
    Orders.OrderID 
FROM 
    Customers 
FULL OUTER JOIN 
    Orders ON Customers.CustomerID=Orders.CustomerID 
ORDER BY 
Customers.CustomerName;

注：FULL OUTER JOIN关键字返回左表（Customers）所有行，以及所有来自右表（Orders）的行。如
果“Customers”中的某些行在“Orders”中没有匹配项，或者在“Orders”中的某些行在“Customers”中没有
匹配项，则这些行也将被列出。

# 左连接
# 右链接
# 内连接
# 全连接
# 笛卡尔积
--------------------------
上面讲的都是连表方式----》我们连表的目的是 查询---》连表的依据--》表和表之间要有关系---》外键关系
-------------------------
图书表 book
id   name   price   publish_id
1    西游记   33      1
2    红楼梦   56      1
3    三国     66      2
4    西厢记   55      6           # 脏数据


出版社表 publish
id    name         addr
1     北京出版社    北京
2      南京出版社   南京
3      东京出版社   东京

# 左连接:select * from book left join publish on book.publish_id=publish.id;
id   name   price   publish_id   id    name         addr
1    西游记   33      1           1     北京出版社    北京
2    红楼梦   56      1           1     北京出版社    北京
3    三国     66      2           2      南京出版社   南京
4    西厢记   55      6           null     null      null


# 右链接 select * from book right join publish on book.publish_id=publish.id;
id   name   price   publish_id    id    name         addr
1    西游记   33      1            1     北京出版社    北京
2    红楼梦   56      1            1     北京出版社    北京
3    三国     66      2            2      南京出版社   南京
null  null   null     null           3      东京出版社   东京   


# 内连接  select * from book inner join publish on book.publish_id=publish.id;
id   name   price   publish_id   id    name         addr
1    西游记   33      1           1     北京出版社    北京
2    红楼梦   56      1           1     北京出版社    北京
3    三国     66      2           2      南京出版社   南京

# 全连接(mysl不支持)
select * from book left join publish on book.publish_id=publish.id
union
select * from book right join publish on book.publish_id=publish.id;
id   name   price   publish_id   id    name         addr
1    西游记   33      1           1     北京出版社    北京
2    红楼梦   56      1           1     北京出版社    北京
3    三国     66      2           2      南京出版社   南京
4    西厢记   55      6           null     null      null
null  null   null     null           3      东京出版社   东京   



# 笛卡尔积
id   name   price   publish_id   id    name         addr
1    西游记   33      1           1     北京出版社    北京
1    西游记   33      1           2      南京出版社   南京
1    西游记   33      1           3      东京出版社   东京 

2
2
2

3
3
3

4
4
4
# sql是  笛卡尔积后，过滤条件是？
select * from book，publish where book.publish_id=publish.id;

总结：上述都是表的链接方式，SQL表连接的根本是查询数据，主要是通过外键关系为基准

posted on 2023-12-04 23:36 橙子先生呀阅读(107) 评论(0) 编辑收藏举报

刷新页面返回顶部