Introduction to Joining Tables in Oracle SQL

As a technical blogger, it’s essential to explain complex concepts like joining tables using real-life examples. In this article, we will explore how to join two tables, ref_table and data_table, using the MERGE INTO statement.

Understanding the Problem

We have three tables:

• ref_table: This table stores reference data.
• data_table: This table contains actual data.
• org_table: This table is used to insert records from data_table and ref_table.

The goal is to join data_table with ref_table based on the e_id column, which appears in both tables. We want to extract values from columns maker, checker, and sme in data_table using regular expressions.

Examining the Code

We are given a MERGE INTO statement that attempts to achieve this goal:

MERGE INTO org_table ot USING (
    SELECT
        e_id,
        regexp_substr(maker, '[^;]+', 1, level)             maker,
        regexp_substr(checker, '[^;]+', 1, level)           checker,
        regexp_substr(sme, '[^;]+', 1, level)               sme
    FROM
        data_table
    CONNECT BY e_id = PRIOR e_id
                   AND PRIOR sys_guid() IS NOT NULL
                   AND level <=
                       regexp_count(maker, ';') + 1
                   AND level <
                       regexp_count(checker, ';') + 1
                   AND level <
                       regexp_count(sme, ';') + 1 ORDER BY E_ID )
    S
    on (ot.e_id = s.e_id)
WHEN NOT MATCHED THEN
INSERT (
    e_id,
    ref_id
)
VALUES (
    s.e_id,
    s.ref_id );

This code uses a MERGE INTO statement, which is similar to an INSERT or UPDATE statement. It attempts to insert rows into the org_table based on conditions specified in the USING clause.

However, this query has several issues:

• The regular expressions used to extract values from columns maker, checker, and sme are incorrect.
• The join condition is not accurate because it references PRIOR e_id, which can lead to incorrect results due to the use of sys_guid().
• The query does not handle cases where the same value appears multiple times in a column.

Correcting the Query

Let’s break down the issues with the original query and provide corrections:

1. Incorrect Regular Expressions

The regular expressions used to extract values from columns maker, checker, and sme are incorrect because they do not account for cases where the same value appears multiple times in a column.

REGEXP_SUBSTR(maker, '[^;]+', 1, LEVEL) -- Incorrect regex pattern

Corrected regular expression pattern:

REGEXP_SUBSTR(maker, '[^;]+')

This corrected regex will extract all non-separator values from the maker column.

Similarly, correct the other two columns:

REGEXP_SUBSTR(checker, '[^;]+') -- Corrected regex for checker
REGEXP_SUBSTR(sme, '[^;]+')    -- Corrected regex for sme

2. Improper Join Condition

The original query uses PRIOR e_id in the join condition, which can lead to incorrect results because it may not always match with e_id from the same row.

CONNECT BY e_id = PRIOR e_id -- Incorrect join condition

Corrected join condition:

CONNECT BY e_id = :e_id -- Corrected join condition

Here, we use a named bind variable (:e_id) to reference e_id from the same row.

3. Handling Duplicate Values

The original query does not handle cases where the same value appears multiple times in a column. To fix this, you can use the LISTAGG function or REGEXP_SUBSTR with a grouping clause.

Here’s an example using REGEXP_SUBSTR with a grouping clause:

SELECT
    LISTAGG(DISTINCT REGEXP_SUBSTR(maker, '[^;]+'), ',') WITHIN GROUP (ORDER BY REGEXP_SUBSTR(maker, '[^;]+')) AS maker_values,
    LISTAGG(DISTINCT REGEXP_SUBSTR(checker, '[^;]+'), ',') WITHIN GROUP (ORDER BY REGEXP_SUBSTR(checker, '[^;]+')) AS checker_values,
    LISTAGG(DISTINCT REGEXP_SUBSTR(sme, '[^;]+'), ',') WITHIN GROUP (ORDER BY REGEXP_SUBSTR(sme, '[^;]+')) AS sme_values
FROM data_table;

This will return a comma-separated list of distinct values from each column.

Corrected MERGE INTO Statement

Now that we’ve addressed the issues with the original query, let’s provide the corrected MERGE INTO statement:

MERGE INTO org_table ot USING (
    SELECT
        e_id,
        LISTAGG(DISTINCT REGEXP_SUBSTR(maker, '[^;]+'), ',') WITHIN GROUP (ORDER BY REGEXP_SUBSTR(maker, '[^;]+')) AS maker_values,
        LISTAGG(DISTINCT REGEXP_SUBSTR(checker, '[^;]+'), ',') WITHIN GROUP (ORDER BY REGEXP_SUBSTR(checker, '[^;]+')) AS checker_values,
        LISTAGG(DISTINCT REGEXP_SUBSTR(sme, '[^;]+'), ',') WITHIN GROUP (ORDER BY REGEXP_SUBSTR(sme, '[^;]+')) AS sme_values
    FROM data_table
    CONNECT BY e_id = :e_id -- Corrected join condition
)
S
ON (ot.e_id = s.e_id)
WHEN NOT MATCHED THEN
INSERT (
    e_id,
    ref_id
)
VALUES (
    s.e_id,
    s.maker_values || ',' || s.checker_values || ',' || s.sme_values
);

In this corrected statement, we use the LISTAGG function to group and aggregate values from each column. We also correct the join condition to reference :e_id instead of PRIOR e_id. Finally, in the WHEN NOT MATCHED THEN clause, we insert the combined value of all columns into the org_table.

With this corrected statement, you should be able to achieve your desired result.

Conclusion

In this article, we explored how to join two tables using Oracle SQL’s MERGE INTO statement. We addressed several issues with the original query and provided corrections for incorrect regular expressions, an improper join condition, and handling duplicate values. With the corrected statement, you should be able to extract values from columns maker, checker, and sme in data_table and insert them into org_table.

Last modified on 2024-04-05