Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-18
5
Medium Priority
?
607 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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