1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to apply Outer Joins to Specific condition.,?

Discussion in 'SQL PL/SQL' started by Vicky, Jun 10, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hiii.,

    Plz., Get me to clear about, how to apply outer join to a Specific condition in a query./

    I'm simulating it using the tables below:


    Code (SQL):



    DESC emp1;

    Name       NULL TYPE        
    ---------- ---- ------------
    SNO             NUMBER      
    EMP_ID          NUMBER      
    EMP_NAME        VARCHAR2(50)
    SALARY          NUMBER      
    DOB             DATE        
    MANAGER_ID      NUMBER      
    CITY            VARCHAR2(10)


    DESC emp2;

    Name      NULL TYPE        
    --------- ---- ------------
    EMP_ID         NUMBER      
    CITY           VARCHAR2(10)
    DEPT_NAME      VARCHAR2(20)
    MANAGER        VARCHAR2(20)


     


    Code (SQL):



    REM INSERTING INTO EMP1

    SET DEFINE OFF;

    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (9,90,'iva',90000,to_date('01-JAN-81 17:04:05','DD-MON-RR HH24:MI:SS'),800,'PNY');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (10,100,'jack',100000,to_date('20-JUN-77 17:04:05','DD-MON-RR HH24:MI:SS'),700,'PNY');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (1,10,'ajin',10000,to_date('10-JUN-78 17:04:05','DD-MON-RR HH24:MI:SS'),100,'NY');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (2,20,'babu',20000,to_date('18-AUG-88 17:04:05','DD-MON-RR HH24:MI:SS'),100,'NY');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (3,30,'celin',30000,to_date('01-JAN-81 17:04:05','DD-MON-RR HH24:MI:SS'),200,'Texas');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (4,40,'dora',40000,to_date('20-JUN-77 17:04:05','DD-MON-RR HH24:MI:SS'),200,'Texas');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (5,50,'elisa',50000,to_date('25-SEP-75 17:04:05','DD-MON-RR HH24:MI:SS'),300,'NJ');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (6,60,'fury',60000,to_date('05-NOV-67 17:04:05','DD-MON-RR HH24:MI:SS'),300,'NJ');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (7,70,'gala',70000,to_date('08-MAR-70 17:04:05','DD-MON-RR HH24:MI:SS'),500,'Florida');
    INSERT INTO EMP1 (SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY) VALUES (8,80,'hola',80000,to_date('18-AUG-88 17:04:05','DD-MON-RR HH24:MI:SS'),600,'PNY');



    REM INSERTING INTO EMP2

    SET DEFINE OFF;

    INSERT INTO EMP2 (EMP_ID,CITY,DEPT_NAME,MANAGER) VALUES (10,'NY','IT','100');
    INSERT INTO EMP2 (EMP_ID,CITY,DEPT_NAME,MANAGER) VALUES (20,'NY','IT','100');
    INSERT INTO EMP2 (EMP_ID,CITY,DEPT_NAME,MANAGER) VALUES (30,'Texas','ECE','200');
    INSERT INTO EMP2 (EMP_ID,CITY,DEPT_NAME,MANAGER) VALUES (40,'Texas','ECE','200');
    INSERT INTO EMP2 (EMP_ID,CITY,DEPT_NAME,MANAGER) VALUES (50,'NJ','CSE','300');
    INSERT INTO EMP2 (EMP_ID,CITY,DEPT_NAME,MANAGER) VALUES (60,'NJ','CSE','300');
    INSERT INTO EMP2 (EMP_ID,CITY,DEPT_NAME,MANAGER) VALUES (70,'PNY','EEE','500');
    INSERT INTO EMP2 (EMP_ID,CITY,DEPT_NAME,MANAGER) VALUES (80,'NH','IT','500');



     



    Code (SQL):



    SELECT e1.emp_id,e2.dept_name,e1.city FROM emp1 e1,emp2 e2
    WHERE e1.emp_id=e2.emp_id;



    EMP_ID DEPT_NAME            CITY    
    ------ -------------------- ----------
        10 IT                   NY        
        20 IT                   NY        
        30 ECE                  Texas      
        40 ECE                  Texas      
        50 CSE                  NJ        
        60 CSE                  NJ        
        70 EEE                  Florida    
        80 IT                   PNY        

     8 ROWS selected            



     

    But., adding an another condition in where clause fetching less number of records even on outer joining.,..


    Code (SQL):



    SELECT e1.emp_id,e2.dept_name,e1.city FROM emp1 e1,emp2 e2
    WHERE e1.emp_id=e2.emp_id
    AND e1.city=e2.city(+);



    EMP_ID DEPT_NAME            CITY    
    ------ -------------------- ----------
        10 IT                   NY        
        20 IT                   NY        
        30 ECE                  Texas      
        40 ECE                  Texas      
        50 CSE                  NJ        
        60 CSE                  NJ        

     6 ROWS selected

     


    I expect it would return emp_id up to 80(as per the result of 1st query), with 'null' in the remaining columns.. But it doesn't happen that way.. Cud U tel me wat's the correct way achieve this.,!
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Your example is performing two joins between EMP1 and EMP2. Only one of the joins you have supplied is an OUTER join. The legacy OUTER JOIN syntax (+) is very prone to such problems -- which is why I abandoned it with glee when Oracle started supporting ANSI SQL.

    Code (Text):
    SELECT e1.emp_id,e2.dept_name,e1.city AS E1_CITY, e2.city AS E2_CITY
    FROM   emp1 e1
           LEFT OUTER JOIN emp2 e2
           ON e1.city = e2.city
           AND e1.emp_id = e2.emp_id
    ORDER BY 1;

        EMP_ID DEPT_NAME            E1_CITY    E2_CITY  
    ---------- -------------------- ---------- ----------
            10 IT                   NY         NY        
            20 IT                   NY         NY        
            30 ECE                  Texas      Texas      
            40 ECE                  Texas      Texas      
            50 CSE                  NJ         NJ        
            60 CSE                  NJ         NJ        
            70                      Florida              
            80                      PNY                  
            90                      PNY                  
           100                      PNY              
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    So, isn't possible to apply outer join to a specific condition(i.e., making a conditional optional in where clause).,? Is der any way to achieve the same(i.e., to get the result 1st query with 'null' in the non-existing fields)./!
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    It's possible to do all kinds of things. However, you are not understanding how an OUTER JOIN works. You aren't alone in that -- many people don't fully understand the logic of what it does.

    The query that I supplied earlier (with the EMP_ID column from EMP2 added) returns all of the rows from EMP1 and EMP2 that meet both JOIN conditions plus rows from EMP1 (the table referenced on the left side of the join conditions) that do not have rows meeting the JOIN conditions in EMP2.

    Code (Text):
    SELECT e1.emp_id AS E1_EMP, e2.emp_id AS E2_EMP,e2.dept_name,e1.city AS E1_CITY, e2.city AS E2_CITY
    FROM   emp1 e1
           LEFT OUTER JOIN emp2 e2
           ON e1.city = e2.city
           AND e1.emp_id = e2.emp_id
    ORDER BY 1;

        E1_EMP     E2_EMP DEPT_NAME            E1_CITY    E2_CITY  
    ---------- ---------- -------------------- ---------- ----------
            10         10 IT                   NY         NY        
            20         20 IT                   NY         NY        
            30         30 ECE                  Texas      Texas      
            40         40 ECE                  Texas      Texas      
            50         50 CSE                  NJ         NJ        
            60         60 CSE                  NJ         NJ        
            70                                 Florida              
            80                                 PNY                  
            90                                 PNY                  
           100                                 PNY

    If the query were changed to a RIGHT JOIN, it would return all of the rows from EMP1 and EMP2 that meet both JOIN conditions plus rows from EMP2 (the table referenced on the right side of the join conditions) that do not have rows meeting the JOIN conditions in EMP1.

    Code (Text):

    SELECT e1.emp_id AS E1_EMP, e2.emp_id AS E2_EMP,e2.dept_name,e1.city AS E1_CITY, e2.city AS E2_CITY
    FROM   emp1 e1
           RIGHT OUTER JOIN emp2 e2
           ON e1.city = e2.city
           AND e1.emp_id = e2.emp_id
    ORDER BY 1;

        E1_EMP     E2_EMP DEPT_NAME            E1_CITY    E2_CITY  
    ---------- ---------- -------------------- ---------- ----------
            10         10 IT                   NY         NY        
            20         20 IT                   NY         NY        
            30         30 ECE                  Texas      Texas      
            40         40 ECE                  Texas      Texas      
            50         50 CSE                  NJ         NJ        
            60         60 CSE                  NJ         NJ        
                       70 EEE                             PNY        
                       80 IT                              NH    
    Your original question asked why your query did not return employees 70 and 80 that exist in both tables. They do... but the cities are different. The query contains the join condition "ON e1.city = e2.city". An OUTER JOIN does not instruct Oracle to 'ignore this JOIN condition' which would be required in order to join those rows in the two different tables.
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    As soon as I hit reply I realized I'd left off my third query. The request you have asked for is not what I consider a join condition. You cannot JOIN two tables by values that don't match unless you manipulate the data (like with a FUNCTION) to make them match in the JOIN condition.

    The following modifies the first query that returned (from your perspective) rows that you did not want (employees 90 and 100). It then uses a filter to remove rows for which the employee does not exist in the second table.

    Code (Text):
    SELECT e1.emp_id AS E1_EMP, e2.emp_id AS E2_EMP,e2.dept_name,e1.city AS E1_CITY, e2.city AS E2_CITY
    FROM   emp1 e1
           LEFT OUTER JOIN emp2 e2
           ON e1.city = e2.city
           AND e1.emp_id = e2.emp_id
    WHERE e1.emp_id IN (SELECT emp_id FROM emp2)
    ORDER BY 1;


        E1_EMP     E2_EMP DEPT_NAME            E1_CITY    E2_CITY  
    ---------- ---------- -------------------- ---------- ----------
            10         10 IT                   NY         NY        
            20         20 IT                   NY         NY        
            30         30 ECE                  Texas      Texas      
            40         40 ECE                  Texas      Texas      
            50         50 CSE                  NJ         NJ        
            60         60 CSE                  NJ         NJ        
            70                                 Florida              
            80                                 PNY        
     
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Matthew,
    Thanks for the explanation.. Here, U've used 'IN' condition to achieve the result, using the tables which I have simulated..

    Actually I want to the achieve the result, which I expected in another environment, where they've used 4 tables..
    Because of one join condition, the query is not fetching any record... So, I wanna make that join condition optional(i.e., the result without this join condition should be displayed if this join doesn't met.,if met including this join the result should be displayed :))..Der I cant totally change the structure of the query.. And I do understand Oracle 'OUTER JOIN' does not help in 'Ignoring Join conditions'..

    So, for this exact scenario.,is der any way to achieve the same.?
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I can not think of any way to make a JOIN condition optional and I can't write queries in the absence of data to query against.
     
  8. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I don't think i can export data from der.. I'd rather do a functional approach for dis.. And as per this thread, its only possible to apply outer join on the whole, but not for specific conditions(i.e., it won't help in ignoring those).. Thnkx Matthew..