SQL - 多表查询

OLDERHARD

于 2024-08-18 11:38:57 发布

阅读量217

点赞数 2

文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/m0_74403543/article/details/141297531

版权

在现实世界中，通常会需要从多张表格里选取列，也就是当想要获取一些列信息时，这些列在不同的表中，就需要连接，将它们连成一个表，之后再进行操作。连接分为内连接和外连接

内连接(join)

inner join products p -- inner可省略

SELECT o.product_id,name,quantity,o.unit_price
from order_items o	-- 别名 o 
inner join products p 	-- inner可省略 
on o.product_id=p.product_id	-- 条件

跨数据库连接
- 如何将分散在多个数据库的表中的列合并起来
- inner join sql_inventory.products p -- 用它所在的数据库充当前缀，即可使用
- ```
SELECT o.product_id,name,quantity,o.unit_price
from order_items o	-- 别名 o 
inner join products p 	-- inner可省略 
on o.product_id=p.product_id	-- 条件
```

自连接
- 可以将一张表与它自己连接，例如在公司中，一个人即使员工又是管理者
- 自连接跟连接其他的表大致不同，唯一区别就是我们要使用不同的别名。还要给每个列名取个别名，加以区分。
- ```
use sql_hr;
select e.employee_id,e.first_name,e.last_name,
m.employee_id as manager_id
from employees e	-- 使用别名后，再引用表名只能使用别名
join employees m on e.reports_to=m.employee_id
```

多表连接

连接超过两张表，继续使用 join 关键字添加表即可

select c.client_id,invoice_id,date,amount,p.payment_method
from payments p
join payment_methods pm on p.payment_id=pm.payment_method_id
join clients c on p.client_id=c.client_id;

复合条件连接
- 当一个表中不能用唯一列识别每一行时，比如用两列值才能识别每一行，怎样将这个表同其他表相连接呢？要将这个表中这两个列都要连接作为连接条件，才能连接这个表
- ```
select *
from order_items oi
join order_items_notes oin
on oi.order_id=oin.order_Id and oi.product_id=oin.product_id
```

using 子句

如果两个表中有列名称时完全一样的，我们就可以用一个更简洁的 using 子句替换 on 子句

select o.customer_id,o.order_id,c.first_name,s.name as shipper
from orders o
join customers c 
-- on o.customer_id=c.customer_id 代替
using (customer_id)
left join shippers s
-- on o.shipper_id=s.shipper_id 代替
using (shipper_id)

隐式连接语法
- 尽量不要使用，尽量使用显式连接
- ```
-- 显式
select *
from orders o
join customers c on o.customer_id=c.customer_id;
-- 隐式
select *
from orders o,customers c
where o.customer_id=c.customer_id;
```
- 显式连接
  - from orders o join customers c on o.customer_id=c.customer_id;
- 隐式连接
  - from orders o,customers c where o.customer_id=c.customer_id;

外连接(outer join)
- 当两个表内连接时，结果集只会返回连接条件正确的记录，其他记录不返回，外连接，会返回表中所有记录，对应字段没有值会是null
- 两种连接方式
  - 左连接：所有左表的记录，不管连接是否正确，都会返回
    - from products p left join order_items oi on p.product_id=oi.product_id;
  - 右连接：所有右表的记录，不管连接是否正确，都会返回
    - from products p right join order_items oi on p.product_id=oi.product_id;
    - ```
    select p.product_id,name,quantity
    from products p
    -- left join order_items oi on p.product_id=oi.product_id;
    right join order_items oi on p.product_id=oi.product_id;
```
- 多表外连接
  - 最好一直使用左连接，使得连表的情况不那么复杂
  - ```
  select order_date,order_id,first_name,s.shipper_id as shipper,os.name as status
  from orders o
  left join customers c on c.customer_id=o.customer_id
  left join shippers s on o.shipper_id=s.shipper_id
  left join order_statuses os on os.order_status_id=o.status
```
- 自外连接
  - 跟自连接类似，只是还要返回没有匹配的空值
  - ```
  select 
      s.employee_id,
      s.first_name,
      m.employee_id as manager
  from employees s
  left join employees m on s.reports_to=m.employee_id
```
- 自然连接
  - 一个更简便的方法连接两个表，通过使用自然连接，我们就不用具体写出连接条件了，数据库引擎会自己会基于共同的列连接，但是我们自己无法控制它，可以生成意外的结果
  - ```
  select *
  from orders o
  natural join customers c
```
- 交叉连接
  - 第一个表的每条记录和第二个表的每条记录都会结合
  - from customers c cross join products p (显式连接)
  - from customers c,products p (隐式连接)
- 联合(union)
  - 通过 union关键，我们可以合并多段查询的记录
  - 注意：
    - 这些查询可以基于同一张表格，也可以是不同表格
    - 所有的select语句中的列数和顺序必须相同，列的数据类型必须相同或兼容
    - ```
    -- 合并同表的几段查询，根据分数不同，划分金、银、铜
    select customer_id,
    	first_name,
        points,
        'Gold' as type
    from customers c
    where points>=3000
    union
    select customer_id,
    	first_name,
        points,
        'Silver' as type
    from customers c
    where points >=2000 and points <3000
    union
    select customer_id,
    	first_name,
        points,
        'Bronze' as type
    from customers c
    where points<2000
    order by points desc
```