如何用数据库sql语句实现以下的两张表的连接呢?

发布网友 发布时间：2022-04-09 11:42

我来回答

共6个回答

懂视网 时间：2022-04-09 16:03

select t1.* from t1,t2 where t1.object_id=t2.object_id; 99 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1506669289 -------------------------------------------------------------------------------- ------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim e | -------------------------------------------------------------------------------- ------- | 0 | SELECT STATEMENT | | 99 | 16929 | 201 (0)| 00: 00:03 | | 1 | NESTED LOOPS | | 99 | 16929 | 201 (0)| 00: 00:03 | | 2 | NESTED LOOPS | | 99 | 16929 | 201 (0)| 00: 00:03 | | 3 | TABLE ACCESS FULL | T2 | 99 | 1287 | 3 (0)| 00: 00:01 | |* 4 | INDEX RANGE SCAN | IDX_T1 | 1 | | 1 (0)| 00: 00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 158 | 2 (0)| 00: 00:01 | -------------------------------------------------------------------------------- ------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 135 consistent gets 4 physical reads 0 redo size 6108 bytes sent via SQL*Net to client 589 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99 rows processed

 

【测试】在hr用户下自行创建T1和T2表写一条SQL语句，（NL连接）

标签：

热心网友 时间：2022-04-09 13:11

用全连接：full join, 效率比union高

SELECT NVL(A.name,B.name) AS NAME,NVL(A.list_a,0) AS list_a, 
NVL(B.list_b,0) AS list_b
FROM table_a A 
full join table_b B 
ON A.name=B.name

NVL作用,参数1为空时取参数2的值

望采纳,不懂可以问.

热心网友 时间：2022-04-09 14:29

昨天刚回答了一个这样的问题，用全外连接即可 ，语句已测试
select nvl(t1.name,t2.name) name,
nvl(list_a, 0) list_a,
nvl(list_b, 0) list_b from table_a t1 full join table_b t2 on t1.name = t2.name
order by nvl(t1.name,t2.name)追问多谢你回答，不过确实下边这位先给我了主思路，还是很感谢你哈~~

热心网友 时间：2022-04-09 16:04

sele ct *from (
sele ct name,list_a ,0 list_b from table_a
uni on
sele ct name,0 ,list_b from table_b
)

试试看，祝你好运！追问没能运行起来，不过还是多谢~

热心网友 时间：2022-04-09 17:55

SELECT X.NAME,NVL(TABLE_A.LIST,0) lista,NVL(TABLE_B.list,0) listb
FROM
(SELECT NAME FROM TABLE_A
UNION
SELECT NAME FROM TABLE_B) X,
TABLE_A,TABLE_B
WHERE X.NAME = TABLE_A.NAME(+)
AND
X.NAME = TABLE_B.NAME(+)
order by x.name;
——————————————————————————————
已测试，可实现

热心网友 时间：2022-04-09 20:03

sql语句的
左外连接追问嘿，多谢~~