Forum Stats

  • 3,770,127 Users
  • 2,253,073 Discussions
  • 7,875,336 Comments

Discussions

Categories

Problem with outer join

User_4LC0G
User_4LC0G Member Posts: 13 Green Ribbon
in SQL & PL/SQL

Hello Experts,

Oracle DB version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production

Table Tracking

CREATE TABLE tracking (awb number(10) primary_key,dispatch_date date);

Sample Data

AWB          DISPATCH_DATE
1             20-JUN-2017
2             21-JUL-2017
3             31-JUL-2017
4             21-AUG-2017
5             21-APR-2020
6             21-MAY-2020

Table Tracking_item

CREATE TABLE tracking_item (item number(8),awb number(10));

Sample Data

IITEM      AWB    
1001        1
1001        4
1002        4
1002        6
1003        2
1004        5

Table Items

CREATE TABLE items (item number(8) primary_key, item_desc varchar2(100));

Sample Data

ITEM      ITEM_DESC
1001        Shirts
1002        Socks
1003        Trousers
1004        Belts
1005        T-Shirts
1006        Handkerchief

Requirement:

Pick items from items table for

  1. If there is no tracking information present in the tracking table.
  2. If tracking information is available then the dispatch_date (of all AWB's for that item) must be greater than 12 months. Even if one AWB is less than 12 months then dont pick it.


Desired Output

ITEM
1001
1003
1005
1006


My effort

select track_item.item
from (
       select ti.item item,
              max(tk.dispatch_date) dis_date
        from tracking tk, 
             tracking_item ti
       where tk.awb = ti.awb
       group by ti.item
     ) track_item
    ,items it
where months_between(sysdate,track_item.dispatch_date) > 12
and items.item = track_item.item(+);


Thank You

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    Accepted Answer

    And if you want outer join:

    with items_out as (
                   select  tracking_item.item
                     from  tracking,
                           tracking_item
                     where tracking_item.awb = tracking.awb
                       and tracking.dispatch_date > add_months(trunc(sysdate),-12)
                 )
 select  items.item
   from  items,
         items_out
   where items_out.item(+) = items.item
     and items_out.item is null
   order by item
/

    SY.

    User_4LC0G
    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Jun 14, 2021 9:42AM

    Hi, @User_4LC0G

    Thanks for posting the CREATE TABLE statements. Remember why you need to go to that trouble: so the people who want to help you can re-create the problem on their own systems and test their ideas. If you don't post working INSERT statements, too, then the CREATE TABLE statements aren't very helpful.

    Use ANSI join syntax for all joins, especially outer joins. That probably won't solve this problem, but it may help you see what the problem is.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Jun 14, 2021 9:53AM

    Hi,

    The condition

    where months_between(sysdate,track_item.dispatch_date) > 12

    is clearly wrong. If you want items that don't have a match in the tracking table, then you want some rows in the result set where dispatch_date is NULL.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond

    If tracking information is available then the dispatch_date (of all AWB's for that item) must be greater than 12 months. Even if one AWB is less than 12 months then dont pick it.:

    There is no need to outer join. All you need is MINUS:

     select  items.item
   from  items
minus
 select  tracking_item.item
   from  tracking,
         tracking_item
   where tracking_item.awb = tracking.awb
     and tracking.dispatch_date > add_months(trunc(sysdate),-12)
/

    And there is no way to get desired output with the data you posted since none of dispatch dates in your sample are younger than 12 months. I will assume:

    AWB          DISPATCH_DATE
1             20-JUN-2017
2             21-JUL-2017
3             31-JUL-2017
4             21-AUG-2017
5             21-AUG-2020 -- corrrected APR to AUG
6             21-JUN-2020 -- corrrected MAY to JUN

    SY.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    Accepted Answer

    And if you want outer join:

    with items_out as (
                   select  tracking_item.item
                     from  tracking,
                           tracking_item
                     where tracking_item.awb = tracking.awb
                       and tracking.dispatch_date > add_months(trunc(sysdate),-12)
                 )
 select  items.item
   from  items,
         items_out
   where items_out.item(+) = items.item
     and items_out.item is null
   order by item
/

    SY.

    User_4LC0G
    · Share on TwitterShare on Facebook
  • User_4LC0G
    User_4LC0G Member Posts: 13 Green Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16798805#Comment_16798805

    Thank you...

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,164 Blue Diamond

    If performance is important, you may want to use semi-join and anti-join instead of joins, as shown below. On the very minimal data you provided, SY's MINUS solution shows a cost of 10, and his outer-join solution a cost of 9; the solution proposed below shows a cost of 6. Obviously no conclusion can be drawn from such small samples (after all, if data was indeed that small, then you wouldn't care about performance anyway), but this does suggest that there may be non-trivial performance differences.

    select item
from   items
where  item not in (
  select item
  from   tracking_item
  where  awb in (
                  select awb
                  from   tracking
                  where  dispatch_date > add_months(trunc(sysdate), -12)
                )
    and  item is not null  -- this is not needed if item is non-nullable in table tracking_item
);


    Your problem requirement can be restated as follows: select all items, except those that are associated with at least one "tracking" with a dispatch date less than 12 months old. The query follows that logic step by step: the inner subquery selects the awb with dispatch date in the last 12 months, then the middle subquery selects all the items that are associated with these awb (that list may have duplicates, but that does not matter), and the main query selects all items that are not on the list produced by the middle subquery.

    Note that the last condition in the middle subquery is not needed, if it is known that item is NOT NULL in the tracking_item table.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond

    Did you create PK on ITEMS table?

    SQL> alter table items add constraint itemp_pk primary key(item);

Table altered.

SQL> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> explain plan for
  2  with items_out as (
  3                     select  tracking_item.item
  4                       from  tracking,
  5                             tracking_item
  6                       where tracking_item.awb = tracking.awb
  7                         and tracking.dispatch_date > add_months(trunc(sysdate),-12)
  8                   )
  9   select  items.item
 10     from  items,
 11           items_out
 12     where items_out.item(+) = items.item
 13       and items_out.item is null
 14  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 2127551257

--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     5 |    85 |     7   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI      |               |     5 |    85 |     7   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | ITEMP_PK      |     6 |    24 |     1   (0)| 00:00:01 |
|   3 |   VIEW               |               |     1 |    13 |     6   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |               |     1 |    22 |     6   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| TRACKING      |     1 |    15 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| TRACKING_ITEM |     6 |    42 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEMS_OUT"."ITEM"="ITEMS"."ITEM")
   4 - access("TRACKING_ITEM"."AWB"="TRACKING"."AWB")
   5 - filter(INTERNAL_FUNCTION("TRACKING"."DISPATCH_DATE")>ADD_MONTHS(TRUNC(S
              [email protected]!),-12))

21 rows selected.

SQL> explain plan for
  2  select item
  3  from   items
  4  where  item not in (
  5    select item
  6    from   tracking_item
  7    where  awb in (
  8                    select awb
  9                    from   tracking
 10                    where  dispatch_date > add_months(trunc(sysdate), -12)
 11                  )
 12  )
 13  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 4157722238


--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     5 |    85 |     7   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA   |               |     5 |    85 |     7   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | ITEMP_PK      |     6 |    24 |     1   (0)| 00:00:01 |
|   3 |   VIEW               | VW_NSO_1      |     1 |    13 |     6   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |               |     1 |    22 |     6   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| TRACKING      |     1 |    15 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| TRACKING_ITEM |     6 |    42 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM"="ITEM")
   4 - access("AWB"="AWB")
   5 - filter(INTERNAL_FUNCTION("DISPATCH_DATE")>ADD_MONTHS(TRUNC([email protected]!),-
              12))

21 rows selected.

SQL>

    SY.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,164 Blue Diamond

    @Solomon Yakobson


    I created the table using the OP's statements (correcting the only syntax error, which was exactly the PK constraint: he wrote primary_key,I just removed the underscore.

    My Oracle version is the same as yours (running on Oracle Linux, but that shouldn't be causing such differences).

    For my version:

      select item
from   items
where  item not in (
  select item
  from   tracking_item
  where  awb in (
                  select awb
                  from   tracking
                  where  dispatch_date > add_months(trunc(sysdate), -12)
                )
    and  item is not null  -- this is not needed if item is non-nullable in table tracking_item
 12  );

Execution Plan
----------------------------------------------------------
Plan hash value: 3660861432

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     6 |    78 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                        |               |       |       |            |          |
|   2 |   INDEX FAST FULL SCAN         | SYS_C00187156 |     6 |    78 |     2   (0)| 00:00:01 |
|   3 |   NESTED LOOPS SEMI            |               |     1 |    48 |     4   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL           | TRACKING_ITEM |     1 |    26 |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID | TRACKING      |     2 |    44 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | SYS_C00187157 |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

    (The code looks odd since I copied from SQL Developer and pasted in SQL*Plus - you can see an errant line number just on the last line of code. I thought it best to leave everything exactly as it shows on my screen.)


    For your query I get this:

    -   with items_out as (
                   select  tracking_item.item
                     from  tracking,
                           tracking_item
                     where tracking_item.awb = tracking.awb
                       and tracking.dispatch_date > add_months(trunc(sysdate),-12)
                 )
 select  items.item
   from  items,
         items_out
   where items_out.item(+) = items.item
     and items_out.item is null
   order by item
 14  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1118356188

----------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |     6 |   156 |     9  (12)| 00:00:01 |
|   1 |  MERGE JOIN ANTI       |               |     6 |   156 |     9  (12)| 00:00:01 |
|   2 |   INDEX FULL SCAN      | SYS_C00187156 |     6 |    78 |     2   (0)| 00:00:01 |
|*  3 |   SORT UNIQUE          |               |     2 |    26 |     7  (15)| 00:00:01 |
|   4 |    VIEW                |               |     2 |    26 |     6   (0)| 00:00:01 |
|*  5 |     HASH JOIN          |               |     2 |    96 |     6   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL | TRACKING      |     2 |    44 |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL | TRACKING_ITEM |     6 |   156 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


    Not sure why the two plans in your post are (almost) identical to each other, and neither matches the plans I got.

    · Share on TwitterShare on Facebook