SOUBHIK RAKSHIT SOUBHIK RAKSHIT - 1 month ago 8
SQL Question

Why does Left Outer Join not work for attribute in different types in the tables in SQL?

I have two tables.

E_Tab

E_NO E_Code Minutes
170 01 1506
170 40 480
464 01 1440
464 51 1440
464 40 480


Second table,

P_Tab

P_No P_Code Minutes
170 01 1506
170 40 480
464 01 1440
464 51 1440


What I want is simply to perform a left outer join. I ran this query:

select * from E_Tab e
left outer join P_Tab p
on e.E_No=p.P_No;


And getting output:

E_NO E_Code Minutes P_No P_Code Minutes
170 01 1506 null null null
170 40 480 null null null
464 01 1440 null null null
464 51 1440 null null null
464 40 480 null null null


Could anyone please suggest what is going wrong? How can I get the out in the way Left outer join generally works? Thank you.

Edit 1:

E_No is in Number and P_No is in varchar2. E_code and P_code are in same datatyep so as Minutes. Can anybody suggest how to modify the query to get the same intended result?


Edit 2:

My Intended output:

E_NO E_Code Minutes P_No P_Code Minutes
170 01 1506 170 01 1506
170 40 480 170 40 480
464 01 1440 464 01 1440
464 51 1440 464 51 1440
464 40 480 null null null


Sorry for the confusion. My query may not be correct.

Answer Source

Updated version (assuming that p_code and e_code are VARCHAR):

soelect * 
from E_Tab e
  left outer join P_Tab p
  on e.E_No=p.P_No
   AND SUBSTR(E.E_CODE,2,99) = P.P_CODE

Output:

E_NO    E_CODE  MINUTES P_NO    P_CODE  MINUTES
1   170 001 1506     170        01  1506
2   170 340 480  170        40  480
3   464 001 1440    464         01  1440
4   464 051 1440    464         51  1440
5   464 340 480 NULL    NULL    NULL

Precedent answer

Your posted query should work if e_no is NUMBER and p_no is CHAR or VARCHAR, with or without embedded spaces. I think you didn't post the query you are using. I can guess from your expected output you forgot

 AND E.E_Code = P.P_Code

or, if you sample data are correct:

 AND SUBSTR(E.E_CODE,2,99)=P.P_CODE

in you join clause. Moreover, the value of E_CODE in your sample output is not the same in your sample data.

Example of output with your query and data, using P_NO as CHAR(8) with spaces:

insert into p_tab values (' 170 ' , 01, 1506)

+--+------+--------+---------+---------+--------+---------+
|  | E_NO | E_CODE | MINUTES |  P_NO   | P_CODE | MINUTES |
+--+------+--------+---------+---------+--------+---------+
|  |  170 |    340 |     480 |  170    |      1 |    1506 |
|  |  170 |      1 |    1506 |  170    |      1 |    1506 |
|  |  170 |    340 |     480 |  170    |     40 |     480 |
|  |  170 |      1 |    1506 |  170    |     40 |     480 |
|  |  464 |    340 |     480 |  464    |      1 |    1440 |
|  |  464 |     51 |    1440 |  464    |      1 |    1440 |
|  |  464 |      1 |    1440 |  464    |      1 |    1440 |
|  |  464 |    340 |     480 |  464    |     51 |    1440 |
|  |  464 |     51 |    1440 |  464    |     51 |    1440 |
|  |  464 |      1 |    1440 |  464    |     51 |    1440 |
+--+------+--------+---------+---------+--------+---------+