Solved

How to distinguish NULL and empty string on an outer join query in oracle

Posted on 2009-05-18
5
598 Views
Last Modified: 2012-08-13
I have a query which has a full outer join.
The thing is, I need to distinguish which value in the joined column is really missing from the other table and which one is there but is empty.

Is there such a way to distinguish this?

SELECT T1.A
FROM T1
FULL OUTER JOIN T2 ON T1.A = T2.B

The plan is if the T1 has the record even if the value is empty, the user can edit the value. If it is really not in the table, it is just in read only mode. To see the difference with the T2.
0
Comment
Question by:napoleontan
5 Comments
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 24418073
Not exists in table:

SELECT T1.A
FROM T1
FULL OUTER JOIN T2 ON T1.A = T2.B
WHERE T1.A IS NULL

Exists in table but the value is empty:

SELECT T1.A
FROM T1
FULL OUTER JOIN T2 ON T1.A = T2.B
WHERE T1.A = ''
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24418172
Hi napoleontan,

Have a look at the query below.  It tries to simulate the scenarios.

Note that the behaviour of NULL and '' is the same.
> NULL will not join to NULL
> '' will not join to ''
> IS NULL on '' is true.


lwadwell
WITH table1 AS (
SELECT '1'  AS t1_id, 'a' AS t1_col FROM dual UNION ALL
SELECT '3'  AS t1_id, 'b' AS t1_col FROM dual UNION ALL
SELECT '5'  AS t1_id, 'c' AS t1_col FROM dual UNION ALL
SELECT ''   AS t1_id, 'd' AS t1_col FROM dual UNION ALL
SELECT NULL AS t1_id, 'e' AS t2_col FROM dual
),
table2 AS (
SELECT '2'  AS t2_id, 'v' AS t2_col FROM dual UNION ALL
SELECT '3'  AS t2_id, 'w' AS t2_col FROM dual UNION ALL
SELECT '4'  AS t2_id, 'x' AS t2_col FROM dual UNION ALL
SELECT ''   AS t2_id, 'y' AS t2_col FROM dual UNION ALL
SELECT NULL AS t2_id, 'z' AS t2_col FROM dual
)
SELECT t1_id, t1_col, t2_id, t2_col,
       CASE WHEN t1_id IS NULL THEN 't1_id is null'
            ELSE 't1_id is not null'
       END  AS is_null_test,
       CASE WHEN COALESCE(t1_id,t1_col) IS NULL 
                 AND COALESCE(t2_id,t2_col) IS NOT NULL THEN 'NO DATA IN table1'
            WHEN COALESCE(t2_id,t2_col) IS NULL  
                 AND COALESCE(t1_id,t1_col) IS NOT NULL THEN 'NO DATA IN table2'
            WHEN COALESCE(t2_id,t2_col) IS NULL  
                 AND COALESCE(t1_id,t1_col) IS NULL     THEN 'NO DATA IN either'
            ELSE 'Joined OK - DATA IN BOTH'
       END AS join_desc
  FROM table1 FULL OUTER JOIN table2
    ON t1_id = t2_id

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 24421726
Can you post some sample data and the expected output?
0
 
LVL 1

Author Comment

by:napoleontan
ID: 24428726
Hi lwadwell,

So you mean there is no way to distinguish NULL and ''?

Here is my scenario I have a master table T_MASTER with columns ID and S_CODE
The master table is editable via the forms only.

Then I have another table where I upload data into this T_UPLOAD with column ID an S_CODE.
The UPLOAD table is referring the the actual data based on manual inventoring or checking so the user chat it down in some CSV file then upload to T_UPLOAD.

The T_UPLOAD has the same schema as the MASTER TABLE with column ID and S_CODE


So what I am trying to achieve is to full outer join the two table to see only the difference. I join them via ID.

The resulting column would be
ID                        S_CODE (from master)                  S_CODE (from upload)                 Edit
1                         A                                                    B                                                   [Edit button]
2                         C                                                                                                          [Edit button]
3                                                                                D                                                   [Edit button]
4                                                                                E                                                    [No Edit button]

Now, the scenario here is that for ID 3, the S_CODE for master was blanked because it exist in the MASTER table but has an empty string value. For ID 4, It does not exist in the master table but exist in the UPLOAD table.

Note: the ID table was a COALESCE of the ID of both the master table and the UPLOAD table.

Now I want to distinguish if the S_CODE (master) column is NULL or a BLANK because it will dictate if the edit button will be displayed.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 500 total points
ID: 24428772
Hi,

yes - there is no real difference.

But that doesn't mean you canot do what you want ... try the SQL below as an example.

You can test on the join column - if it is null then it was not joined ... null does not join to null.  Hence by that column being null, you can decide whether it 'exists' in that table or not.
WITH T_MASTER AS (

SELECT '1'  AS t_id, 'A' AS S_CODE FROM dual UNION ALL

SELECT '2'  AS t_id, 'C' AS S_CODE FROM dual UNION ALL

SELECT '3'  AS t_id, ''  AS S_CODE FROM dual

),

T_UPLOAD AS (

SELECT '1'  AS t_id, 'B' AS S_CODE FROM dual UNION ALL

SELECT '2'  AS t_id, ''  AS S_CODE FROM dual UNION ALL

SELECT '3'  AS t_id, 'D' AS S_CODE FROM dual UNION ALL

SELECT '4'  AS t_id, 'E' AS S_CODE FROM dual

)

SELECT COALESCE(tm.t_id, tu.t_id)  AS ID,

       tm.s_code,

       tu.s_code,

       CASE WHEN tm.t_id IS NULL THEN 'No Edit'

            ELSE 'Edit'

       END AS edit_flag

  FROM T_MASTER tm FULL OUTER JOIN T_UPLOAD tu

    ON tm.t_id = tu.t_id

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now