Query to join two tables in SQL

Applications Programming
1

Hi,

I have 2 tables.

Table1
name  map_code    data_code        id
aaa	 2732C	   3333B		10
bbb    1223F	   2545v		11
ccc  				  	        12

Table2

name   map_code   data_code        id    text_description
aaa	  2732C	   3333B		10    description 1
bbb     1223F	   2545v		11    description 2 
ccc  				 	        12    description 3

I want to perform join on these 2 tables. The column to be used to join is map_code & data_code.

The map_code & data_code are null for the id: 13 name: ccc

The output should be like this:

name   map_code   data_code   id 	  text_description
aaa      2732C       3333B         10	     description 1
bbb	  1223F        2545v         11       description 2 
ccc  	  				   12       description 3

How to get the above output?

Regards
Vanitha

2

try this .. Its not tested bcoz i dont have sql in my machine..

select tbl1.name,tbl1.map_code,tbl1.data_code,tbl2.id,tbl2.text_description from table1 tbl1, table2 tbl2 where tbl1.map_code=tbl2.map_code and tbl1.data_code=tbl2.data_code;
3

This code will not work as there are null values in the column of join..However it may work for this particular problem as it is having only one Null value.But in case there are multiple null values in the table,This query will fail

4

Your data looks like this:

SQL>
SQL> SELECT * FROM table1;
 
NAME       MAP_CODE   DATA_CODE          ID
---------- ---------- ---------- ----------
aaa        2732C      3333B              10
bbb        1223F      2545v              11
ccc                                      12
 
3 rows selected.
 
SQL>
SQL> SELECT * FROM table2;
 
NAME       MAP_CODE   DATA_CODE          ID TEXT_DESCRIPTION
---------- ---------- ---------- ---------- -----------------
aaa        2732C      3333B              10 description 1
bbb        1223F      2545v              11 description 2
ccc                                      12 description 3
 
3 rows selected.
 
SQL>
SQL>

You want to fetch records from both tables that have:
(1) NULL values for both map_code and data_code columns, or
(2) Non-NULL and identical values for map_code and data_code columns respectively.

In a join condition, Oracle takes care of case no. (2) already. So for case no. (1), you could use the NVL function and set the column value to something that both sides agree upon mutually.

Here's an example:

SQL>
SQL> --
SQL> -- Query 1: Use NVL function that defaults to a character that you are sure will never exist in
SQL> --          the columns used in the join condition
SQL> --
SQL> SELECT x.name,
  2         x.map_code,
  3         x.data_code,
  4         x.id,
  5         y.text_description
  6    FROM table1 x,
  7         table2 y
  8   WHERE NVL (x.map_code,  '~') = NVL (y.map_code,  '~')
  9     AND NVL (x.data_code, '~') = NVL (y.data_code, '~')
 10  /
 
NAME       MAP_CODE   DATA_CODE          ID TEXT_DESCRIPTION
---------- ---------- ---------- ---------- -----------------
aaa        2732C      3333B              10 description 1
bbb        1223F      2545v              11 description 2
ccc                                      12 description 3
 
3 rows selected.
 
SQL>

Of course, you'd want to ensure that the "mutually agreed upon" value is something that *NEITHER* of the two columns could assume.
(To understand why, imagine the output of Query 1 if table1.map_code is null and table2.map_code is "~").

A way out is to use a non-printable character, like so -

SQL>
SQL> --
SQL> -- Query 2: If no such character could be determined, then use a non-printable character, the
SQL> --          rationale being it's highly unlikely it is used as business data
SQL> --
SQL> select x.name,
  2         x.map_code,
  3         x.data_code,
  4         x.id,
  5         y.text_description
  6    from table1 x,
  7         table2 y
  8   where NVL (x.map_code,  CHR(0)) = NVL (y.map_code,  CHR(0))
  9     and NVL (x.data_code, CHR(0)) = NVL (y.data_code, CHR(0))
 10  /
 
NAME       MAP_CODE   DATA_CODE          ID TEXT_DESCRIPTION
---------- ---------- ---------- ---------- -----------------
aaa        2732C      3333B              10 description 1
bbb        1223F      2545v              11 description 2
ccc                                      12 description 3
 
3 rows selected.
 
SQL>

Otherwise, if you are a truly paranoid programmer, then you probably won't rely on default values; you would be as explicit as you could be -

SQL>
SQL> --
SQL> -- Query 3: Or use the most explicit version
SQL> --
SQL> SELECT x.name,
  2         x.map_code,
  3         x.data_code,
  4         x.id,
  5         y.text_description
  6    FROM table1 x,
  7         table2 y
  8   WHERE (
  9           (x.map_code IS NULL AND y.map_code IS NULL)
 10           OR
 11           (x.map_code = y.map_code)
 12         )
 13     AND (
 14           (x.data_code IS NULL AND y.data_code IS NULL)
 15           OR
 16           (x.data_code = y.data_code)
 17         )
 18  /
 
NAME       MAP_CODE   DATA_CODE          ID TEXT_DESCRIPTION
---------- ---------- ---------- ---------- -----------------
aaa        2732C      3333B              10 description 1
bbb        1223F      2545v              11 description 2
ccc                                      12 description 3
 
3 rows selected.
 
SQL>

tyler_durden