本文共 2470 字，大约阅读时间需要 8 分钟。

ORACLE??? JOIN?????????

???????ORACLE?????JOIN????????????????????????????????

??JOIN??????

????????JOIN????????????????????????????JOIN?????????

1. LEFT JOIN

LEFT JOIN???????????????????????????????????????????????????????

???

SELECT A.a, B.aFROM ALEFT JOIN B ON A.b = B.b(+);

???????A??????????????????????B.a????????null?

2. INNER JOIN

INNER JOIN???????????????????????????JOIN?????????????????????

???

SELECT A.a, B.aFROM AINNER JOIN B ON A.b = B.b(+);

???????A?B??b?????????

3. RIGHT JOIN

RIGHT JOIN??????????????????????????????????????????????????

???

SELECT A.a, B.aFROM ARIGHT JOIN B ON A.b = B.b(+);

???????B????????A????????????A.a????????null?

4. CROSS JOIN

CROSS JOIN?????????????????????????????????????????????????????

???

SELECT A.a, B.aFROM ACROSS JOIN B;

???????A?B?????????????????????????

??JOIN???????

???????JOIN???????????????????????????

1. ????

???JOIN????????????ORACLE?????????????????JOIN??????A.b?B.b???????

2. ????????

??EXPLAIN PLAN?????????????????????Join Operation Count??1000????????

3. ??HASH JOIN?MERGE JOIN

???????????HASH JOIN?MERGE JOIN???Nested JOIN??????????JOIN????????

4. ????

??????????JOIN????????????????????????????

??ORACLE SUM OVER?PARTITION???

SUM OVER?PARTITION?ORACLE?????????????????????

1. ????

SUM OVER??????????????????????????????????

???

SELECT v1, v2, SUM(v2) OVER (ORDER BY v2) AS sumFROM wmg_test;

2. ????

PARTITION??????????????????????????????????????????

???

SELECT v1, v2, SUM(v2) OVER (PARTITION BY v1 ORDER BY v2) AS sumFROM wmg_test;

3. ????

?????PARTITION???????????????????????

???

SELECT v1, v2, SUM(v2) OVER (PARTITION BY v1, v3 ORDER BY v2) AS sumFROM wmg_test;

4. ????

???OVER?????ORDER BY?????????????????v2???????

???

SELECT v1, v2, SUM(v2) OVER (ORDER BY v2 DESC) AS sumFROM wmg_test;

????????

??1?JOIN???????

?????????????????????????????????????

SELECT o.order_id, p.product_id, p.product_name, o.order_qty, s.shipping_idFROM orders oJOIN order_items oi ON o.order_id = oi.order_idJOIN products p ON oi.product_id = p.product_idJOIN shipments s ON o.order_id = s.order_idWHERE o.order_date = '2023-10-01';

??2?SUM OVER?PARTITION?????

???????????????????????????????

SELECT c.customer_id, p.product_id, COUNT(*) AS product_countFROM wmg_testPARTITION BY c.customer_id, p.product_idORDER BY product_count DESC;

????????

?????????????ORACLE??????????????????