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!

Question on non-equi join

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Feb 7, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    I have two tables A and B. Where A is a parent table and B is a child table.
    I need all the records from table A that is not present in table B.

    I would like to know the best way in terms of performance to achieve this. (I have millions of records in the tables)
     
  2. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    Hi, try these two ways, for me the performance is acceptable

    select cd_pk from table_1
    minus
    select cd_pk from table_2

    select cd_pk from table_1
    where cd_pk not in( select cd_pk from table_2)

    or to test performance

    select count(1) from (
    select cd_pk from table_1
    minus
    select cd_pk from table_2)

    Select count(1) from (
    select cd_pk from table_1
    where cd_pk not in( select cd_pk from table_2))
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If these truly are parent/child tables then no record in table b will be in table a since table b holds additional information for the records in table a. What you should be looking for, and what the queries provided you will return, are the primary key values in table a that don't exist in table b.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    You provided not enough data to get a recommendation. there are indexes on these tables and what indexes are on the structure?can you clarify this information ?


    The set operations(intersect/minus) may be expensive in performance.


    For tables of small volumes and with small number of fields is probably a good solution.For a large table and tables contain many fields , it is better to use : join/exists/may be in(...) . If you are using inequality predicates , then you need to use a different approach to the solution.'
     
    Last edited: Feb 8, 2017