SQL practice ："Case when" & "join"

最新推荐文章于 2025-06-17 11:49:31 发布

dingxy

最新推荐文章于 2025-06-17 11:49:31 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库和Oracle 文章标签： join sql table system

本文链接：https://blog.youkuaiyun.com/dingxy/article/details/7337393

数据库和Oracle 专栏收录该内容

88 篇文章

订阅专栏

本文深入探讨了SQL中的Casewhen语句的使用，并详细解释了不同类型的Join操作（左连接、右连接、进一步左连接、全连接）及其应用场景。通过实例演示，帮助读者掌握如何灵活运用这些查询技术。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. Case when

select table_name,

(CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END) ownerDesc

from all_tables;

2. join

create table test1(
reason varchar(10);
volume number(5,0);
amount number (5,0)
)
create table test2(
reason varchar(10);
volume number(5,0);
amount number (5,0)
)

--left join
select * from test1 aa ,test2 bb
where aa.reason =bb.reason(+)

select * from test1 aa left join test2 bb on aa.reason=bb.reason

--further left join
select aa.reason,nvl(aa.volume,0)+ nvl(bb.volume,0) cnt,nvl(aa.amount,0)+ nvl(bb.amount,0) amt
from test1 aa left join test2 bb on aa.reason = bb.reason

--right join
select * from test1 aa ,test2 bb
where aa.reason(+) =bb.reason

select * from test1 aa right join test2 bb on aa.reason = bb.reason

--full join
select * from test1 aa ,test2 bb where aa.reason=bb.reason(+)
union
select * from test1 aa ,test2 bb where aa.reason(+)=bb.reason;