Solved

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

Posted on 2009-05-18
5
597 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 31

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.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 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

13 Experts available now in Live!

Get 1:1 Help Now