Соединение таблиц в Oracle. Типы соединений Inner,left,right,outer join.

1. Внутреннее соединение (возвратит 4 строки)

WITH A AS (
    SELECT 1 aid from dual union all              
    SELECT 2 aid from dual union all              
    SELECT 3 aid from dual union all              
    SELECT 4 aid from dual               
),
B AS (
    SELECT 1 bid from dual union all              
    SELECT 2 bid from dual union all              
    SELECT 3 bid from dual union all              
    SELECT 4 bid from dual               
)
SELECT AID, BID 
  FROM A,B
 WHERE aid=bid;

2. Поменяем значение в таблице B с 4 на 5 внутреннее соединение (возвратит 3 строки)

WITH A AS (
    SELECT 1 aid from dual union all              
    SELECT 2 aid from dual union all              
    SELECT 3 aid from dual union all              
    SELECT 4 aid from dual               
),
B AS (
    SELECT 1 bid from dual union all              
    SELECT 2 bid from dual union all              
    SELECT 3 bid from dual union all              
    SELECT 5 bid from dual               
)
SELECT AID, BID 
  FROM A,B
 WHERE aid=bid;

3. Альтернатива B left join A on aid = bid

WITH A AS (
    SELECT 1 aid from dual union all              
    SELECT 2 aid from dual union all              
    SELECT 3 aid from dual union all              
    SELECT 4 aid from dual               
),
B AS (
    SELECT 1 bid from dual union all              
    SELECT 2 bid from dual union all              
    SELECT 3 bid from dual union all              
    SELECT 5 bid from dual               
)
SELECT AID, BID 
  FROM A,B
 WHERE aid(+) = bid;

4. Альтернатива A left join B on aid = bid

WITH A AS (
    SELECT 1 aid from dual union all              
    SELECT 2 aid from dual union all              
    SELECT 3 aid from dual union all              
    SELECT 4 aid from dual               
),
B AS (
    SELECT 1 bid from dual union all              
    SELECT 2 bid from dual union all              
    SELECT 3 bid from dual union all              
    SELECT 5 bid from dual               
)
SELECT AID, BID 
  FROM A,B
 WHERE aid = bid(+);

5. Внешнее соединение A outer join B on aid = bid

WITH A AS (
    SELECT 1 aid from dual union all              
    SELECT 2 aid from dual union all              
    SELECT 3 aid from dual union all              
    SELECT 4 aid from dual               
),
B AS (
    SELECT 1 bid from dual union all              
    SELECT 2 bid from dual union all              
    SELECT 3 bid from dual union all              
    SELECT 5 bid from dual               
)
SELECT AID, BID 
  FROM A
 OUTER JOIN B
      ON aid = bid;

6. Полное внешнее соединение A full outer join B on aid = bid

WITH A AS (
    SELECT 1 aid from dual union all              
    SELECT 2 aid from dual union all              
    SELECT 3 aid from dual union all              
    SELECT 4 aid from dual               
),
B AS (
    SELECT 1 bid from dual union all              
    SELECT 2 bid from dual union all              
    SELECT 3 bid from dual union all              
    SELECT 5 bid from dual               
)
SELECT AID, BID 
  FROM A
  FULL OUTER JOIN B
      ON aid = bid;
22:12
766
Нет комментариев. Ваш будет первым!
Загрузка...