SQL – Puzzle 8 – Çoklama

2 tabloda da 1 er kayıt var. Joinlenecek sütundaki datalar birebir aynı. Fakat full join yapıldığı zaman çoklama oluyor. Neden çokluyor, çözüm nedir ? ( group by min veya max la çözerim demeyin lütfen 🙂 )

There are 2 tables. Each of them have 1 record and their data of join columns same. But when I use full join, it duplicates recod. Why result looks duplicate, how can we fix it ? ( please don’t say that your solution is group by with min or max )

Örneği simüle etmek için gerekli kod aşağıdaki gibidir.

You can simulate the example by using below code.

WITH table_1 AS
(
SELECT 23 AS unq_column_1,
       10 AS unq_column_2,
       CAST(NULL AS NUMBER(10)) AS unq_column_3, 
       'abc' AS description_a,
       CAST(NULL AS VARCHAR2(100 CHAR)) AS  description_b 
  FROM sys.dual
),
table_2 AS
(
SELECT 23 as unq_column_1,
       10 as unq_column_2,
       CAST(NULL AS NUMBER(10)) AS unq_column_3, 
       CAST(NULL AS VARCHAR2(100 CHAR)) AS description_a,
       'def' as description_b 
  FROM sys.dual
)
SELECT NVL(a.unq_column_1,b.unq_column_1) AS unq_column_1,
       NVL(a.unq_column_2,b.unq_column_2) AS unq_column_2,
       NVL(a.unq_column_3,b.unq_column_3) AS unq_column_3,
       NVL(a.description_a,b.description_a) AS description_a,
       NVL(a.description_b,b.description_b) AS description_b
  FROM table_1 a
       FULL JOIN table_2 b ON a.unq_column_1=b.unq_column_1 
                              AND a.unq_column_2=b.unq_column_2 
                              AND a.unq_column_3=b.unq_column_3;