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 do you get only unmatched rows in different tables?

Discussion in 'SQL PL/SQL' started by OldSchoolCoder, Mar 20, 2014.

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    If you're trying to get only those rows from table A that do NOT match table B, how would you write that? None of the Join options seem to address that unless you can use JOIN ON ID <> ID - is that possible? Or, Could it be done using Where NOT IN (Select...)?

    Example Data

    Table A
    ID
    123
    134
    245
    346
    456
    789
    799

    Table B
    ID
    123
    456
    789

    Results would be:
    134
    245
    346
    799
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    The problem can be solved by not only one method.....

    Variant One:
    Code (SQL):

    WITH
    TableA AS (
    SELECT 123 ID FROM dual UNION ALL
    SELECT 134  FROM dual UNION ALL
    SELECT 245 FROM dual UNION ALL
    SELECT 346 FROM dual UNION ALL
    SELECT 456 FROM dual UNION ALL
    SELECT 789 FROM dual UNION ALL
    SELECT 799 FROM dual
    )
    ,
    TableB AS (
    SELECT 123 ID FROM dual UNION ALL
    SELECT 456 FROM dual UNION ALL
    SELECT 789 FROM dual
    )
    SELECT t.id  FROM tableA t
    LEFT JOIN tableb b ON b.id = t.id
    WHERE b.id IS NULL;

    SQL>

            ID
    ----------
           799
           245
           134
           346
     
    Variant Two :

    Code (SQL):

    WITH
    TableA AS (
    SELECT 123 ID FROM dual UNION ALL
    SELECT 134  FROM dual UNION ALL
    SELECT 245 FROM dual UNION ALL
    SELECT 346 FROM dual UNION ALL
    SELECT 456 FROM dual UNION ALL
    SELECT 789 FROM dual UNION ALL
    SELECT 799 FROM dual
    )
    ,
    TableB AS (
    SELECT 123 ID FROM dual UNION ALL
    SELECT 456 FROM dual UNION ALL
    SELECT 789 FROM dual
    )
    SELECT t.id  FROM tableA t
    WHERE NOT (t.id IN (SELECT b.id FROM  tableb b ));

    SQL>

            ID
    ----------
           799
           245
           134
           346
     
    Variant Three :

    Code (SQL):


    WITH
    TableA AS (
    SELECT 123 ID FROM dual UNION ALL
    SELECT 134  FROM dual UNION ALL
    SELECT 245 FROM dual UNION ALL
    SELECT 346 FROM dual UNION ALL
    SELECT 456 FROM dual UNION ALL
    SELECT 789 FROM dual UNION ALL
    SELECT 799 FROM dual
    )
    ,
    TableB AS (
    SELECT 123 ID FROM dual UNION ALL
    SELECT 456 FROM dual UNION ALL
    SELECT 789 FROM dual
    )
    SELECT t.id  FROM tableA t
    minus
    SELECT b.id  FROM tableb b;

    SQL>

            ID
    ----------
           134
           245
           346
           799



     
     
  3. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Do I understand that you hard coded the ID's? If so, that won't work - the table I used in my question is just an example to help illustrate what I'm trying to accomplish. The actual table is an existing table with hundreds of thousands of rows. I think the minus would work without the other portion though. I'll give it a try. Thank you!!!
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The 'top' part of the example is just that, an example used to get your sample data into the query without building an actual table. The concepts work on real tables, too, so the solutions are valid. If you need an example with tables:


    Code (SQL):



    SQL> CREATE TABLE a (
      2          id NUMBER
      3  );


    TABLE created.


    SQL>
    SQL> CREATE TABLE b (
      2          id NUMBER
      3  );


    TABLE created.


    SQL>
    SQL> INSERT ALL
      2  INTO a
      3  VALUES (123)
      4  INTO a
      5  VALUES (134)
      6  INTO a
      7  VALUES (245)
      8  INTO a
      9  VALUES (346)
     10  INTO a
     11  VALUES (456)
     12  INTO a
     13  VALUES (789)
     14  INTO a
     15  VALUES (799)
     16  INTO b
     17  VALUES (123)
     18  INTO b
     19  VALUES (456)
     20  INTO b
     21  VALUES (789)
     22  SELECT * FROM dual;


    10 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT a.id
      2  FROM a LEFT JOIN b
      3  ON b.id = a.id
      4  WHERE b.id IS NULL;


            ID
    ----------
           799
           245
           134
           346


    SQL>
    SQL> SELECT id
      2  FROM a
      3  WHERE id NOT IN (SELECT id FROM b);


            ID
    ----------
           799
           245
           134
           346


    SQL>
    SQL> SELECT id
      2  FROM a
      3  minus
      4  SELECT id
      5  FROM b;


            ID
    ----------
           134
           245
           346
           799


    SQL>
     
     
  5. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Awesome! Minus worked! I'm so grateful for all the help I've gotten from here and am beginning to feel like I may not be so much of a beginner anymore! Before long, I may be able to help others too!

    Thank you!
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Yes, for writing of examples of sql of requests I followed an example of data from your message.
    These types of sql of requests will work and at real tables.

    If you say that data volume in real tables big, anti-join (not in) and MINUS can work slowly.
    But, correctly to select the option of sql query needs to be looked: on the plan, data access methods, statistics of runtime, etc.


    NB:

    Yes, by the way, David showed you the examples confirming, as all will be good to work at real tables
     
    OldSchoolCoder likes this.
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Sergey mentioned performance of the queries and that does come into play here. Let's look at your two tables with around 1,000,000 records each to find the missing values from table B:


    Code (SQL):

    SQL> CREATE TABLE a (
      2          id NUMBER
      3  );


    TABLE created.


    SQL>
    SQL> CREATE TABLE b (
      2          id NUMBER
      3  );


    TABLE created.


    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000000 loop
      3                  INSERT INTO a
      4                  VALUES(i);
      5                  IF MOD(i,9317) <> 0 THEN
      6                          INSERT INTO b
      7                          VALUES(i);
      8                  END IF;
      9          END loop;
     10
     11          commit;
     12
     13  END;
     14  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> EXEC dbms_stats.gather_schema_stats('GRIBNAUT')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SET autotrace ON timing ON
    SQL>
    SQL> SELECT a.id
      2  FROM a LEFT JOIN b
      3  ON b.id = a.id
      4  WHERE b.id IS NULL;


            ID
    ----------
          9317
         18634
         27951
         37268
         46585
         55902
         65219
         74536
        158389
        149072
        139755


            ID
    ----------
        130438
        121121
         83853
        111804
        102487
         93170
        232925
        223608
        242242
        214291
        204974


            ID
    ----------
        167706
        195657
        186340
        177023
        326095
        316778
        307461
        298144
        288827
        260876
        251559


            ID
    ----------
        279510
        270193
        409948
        391314
        400631
        381997
        372680
        344729
        335412
        363363
        354046


            ID
    ----------
        475167
        484484
        465850
        456533
        437899
        428582
        419265
        447216
        559020
        568337
        549703


            ID
    ----------
        540386
        521752
        512435
        503118
        493801
        531069
        652190
        642873
        633556
        624239
        614922


            ID
    ----------
        605605
        596288
        586971
        577654
        736043
        726726
        717409
        708092
        698775
        689458
        680141


            ID
    ----------
        670824
        661507
        791945
        819896
        810579
        801262
        782628
        773311
        763994
        754677
        745360


            ID
    ----------
        885115
        875798
        903749
        894432
        866481
        857164
        847847
        838530
        829213
        978285
        968968


            ID
    ----------
        959651
        987602
        950334
        941017
        931700
        922383
        913066
        996919


    107 ROWS selected.


    Elapsed: 00:00:00.51


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2798188842
    -------------------------------------------------------------------------------------
    | Id  | Operation            | Name | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      | 10000 |    97K|       |  2515   (1)| 00:00:31 |
    |*  1 |  HASH JOIN RIGHT ANTI|      | 10000 |    97K|    16M|  2515   (1)| 00:00:31 |
    |   2 |   TABLE ACCESS FULL  | B    |   999K|  4882K|       |   448   (2)| 00:00:06 |
    |   3 |   TABLE ACCESS FULL  | A    |  1000K|  4882K|       |   448   (2)| 00:00:06 |
    -------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("B"."ID"="A"."ID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              2  db block gets
           3210  consistent gets
              0  physical reads
              0  redo SIZE
           2564  bytes sent via SQL*Net TO client
            596  bytes received via SQL*Net FROM client
              9  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
            107  ROWS processed


    SQL>
    SQL> SELECT id
      2  FROM a
      3  WHERE id NOT IN (SELECT id FROM b);


            ID
    ----------
          9317
         18634
         27951
         37268
         46585
         55902
         65219
         74536
        158389
        149072
        139755


            ID
    ----------
        130438
        121121
         83853
        111804
        102487
         93170
        232925
        223608
        242242
        214291
        204974


            ID
    ----------
        167706
        195657
        186340
        177023
        326095
        316778
        307461
        298144
        288827
        260876
        251559


            ID
    ----------
        279510
        270193
        409948
        391314
        400631
        381997
        372680
        344729
        335412
        363363
        354046


            ID
    ----------
        475167
        484484
        465850
        456533
        437899
        428582
        419265
        447216
        559020
        568337
        549703


            ID
    ----------
        540386
        521752
        512435
        503118
        493801
        531069
        652190
        642873
        633556
        624239
        614922


            ID
    ----------
        605605
        596288
        586971
        577654
        736043
        726726
        717409
        708092
        698775
        689458
        680141


            ID
    ----------
        670824
        661507
        791945
        819896
        810579
        801262
        782628
        773311
        763994
        754677
        745360


            ID
    ----------
        885115
        875798
        903749
        894432
        866481
        857164
        847847
        838530
        829213
        978285
        968968


            ID
    ----------
        959651
        987602
        950334
        941017
        931700
        922383
        913066
        996919


    107 ROWS selected.


    Elapsed: 00:00:00.50


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 548673372
    ----------------------------------------------------------------------------------------
    | Id  | Operation               | Name | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |      | 10000 |    97K|       |  2515   (1)| 00:00:31 |
    |*  1 |  HASH JOIN RIGHT ANTI NA|      | 10000 |    97K|    16M|  2515   (1)| 00:00:31 |
    |   2 |   TABLE ACCESS FULL     | B    |   999K|  4882K|       |   448   (2)| 00:00:06 |
    |   3 |   TABLE ACCESS FULL     | A    |  1000K|  4882K|       |   448   (2)| 00:00:06 |
    ----------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("ID"="ID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              2  db block gets
           3210  consistent gets
              0  physical reads
              0  redo SIZE
           2564  bytes sent via SQL*Net TO client
            596  bytes received via SQL*Net FROM client
              9  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
            107  ROWS processed


    SQL>
    SQL> SELECT id
      2  FROM a
      3  minus
      4  SELECT id
      5  FROM b;


            ID
    ----------
          9317
         18634
         27951
         37268
         46585
         55902
         65219
         74536
         83853
         93170
        102487


            ID
    ----------
        111804
        121121
        130438
        139755
        149072
        158389
        167706
        177023
        186340
        195657
        204974


            ID
    ----------
        214291
        223608
        232925
        242242
        251559
        260876
        270193
        279510
        288827
        298144
        307461


            ID
    ----------
        316778
        326095
        335412
        344729
        354046
        363363
        372680
        381997
        391314
        400631
        409948


            ID
    ----------
        419265
        428582
        437899
        447216
        456533
        465850
        475167
        484484
        493801
        503118
        512435


            ID
    ----------
        521752
        531069
        540386
        549703
        559020
        568337
        577654
        586971
        596288
        605605
        614922


            ID
    ----------
        624239
        633556
        642873
        652190
        661507
        670824
        680141
        689458
        698775
        708092
        717409


            ID
    ----------
        726726
        736043
        745360
        754677
        763994
        773311
        782628
        791945
        801262
        810579
        819896


            ID
    ----------
        829213
        838530
        847847
        857164
        866481
        875798
        885115
        894432
        903749
        913066
        922383


            ID
    ----------
        931700
        941017
        950334
        959651
        968968
        978285
        987602
        996919
    107 ROWS selected.


    Elapsed: 00:00:01.26


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1035326697
    ------------------------------------------------------------------------------------
    | Id  | Operation           | Name | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |  1000K|  9765K|       |  6997  (51)| 00:01:24 |
    |   1 |  MINUS              |      |       |       |       |            |          |
    |   2 |   SORT UNIQUE       |      |  1000K|  4882K|    11M|  3498   (1)| 00:00:42 |
    |   3 |    TABLE ACCESS FULL| A    |  1000K|  4882K|       |   448   (2)| 00:00:06 |
    |   4 |   SORT UNIQUE       |      |   999K|  4882K|    11M|  3498   (1)| 00:00:42 |
    |   5 |    TABLE ACCESS FULL| B    |   999K|  4882K|       |   448   (2)| 00:00:06 |
    ------------------------------------------------------------------------------------

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              2  db block gets
           3198  consistent gets
              0  physical reads
              0  redo SIZE
           2564  bytes sent via SQL*Net TO client
            596  bytes received via SQL*Net FROM client
              9  SQL*Net roundtrips TO/FROM client
              2  sorts (memory)
              0  sorts (disk)
            107  ROWS processed


    SQL>
     

    Granted this is on a database with no additional load but the queries were fairly fast given the data volume.


    Conventional wisdom would indicate that indexes on both tables would improve performance; unfortunately this is NOT the case since every record of each table is being returned to construct the result set:


    Code (SQL):

    SQL> CREATE INDEX a_id_idx ON a(id);


    INDEX created.


    SQL> CREATE INDEX b_id_idx ON b(id);


    INDEX created.


    SQL>
    SQL> SET autotrace ON timing ON
    SQL>
    SQL> SELECT a.id
      2  FROM a LEFT JOIN b
      3  ON b.id = a.id
      4  WHERE b.id IS NULL;


            ID
    ----------
          9317
         18634
         27951
         37268
         46585
         55902
         65219
         74536
        158389
        149072
        139755


            ID
    ----------
        130438
        121121
         83853
        111804
        102487
         93170
        232925
        223608
        242242
        214291
        204974


            ID
    ----------
        167706
        195657
        186340
        177023
        326095
        316778
        307461
        298144
        288827
        260876
        251559


            ID
    ----------
        279510
        270193
        409948
        391314
        400631
        381997
        372680
        344729
        335412
        363363
        354046


            ID
    ----------
        475167
        484484
        465850
        456533
        437899
        428582
        419265
        447216
        559020
        568337
        549703


            ID
    ----------
        540386
        521752
        512435
        503118
        493801
        531069
        652190
        642873
        633556
        624239
        614922


            ID
    ----------
        605605
        596288
        586971
        577654
        736043
        726726
        717409
        708092
        698775
        689458
        680141


            ID
    ----------
        670824
        661507
        791945
        819896
        810579
        801262
        782628
        773311
        763994
        754677
        745360


            ID
    ----------
        885115
        875798
        903749
        894432
        866481
        857164
        847847
        838530
        829213
        978285
        968968


            ID

    ----------
        959651
        987602
        950334
        941017
        931700
        922383
        913066
        996919


    107 ROWS selected.


    Elapsed: 00:00:00.51


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2798188842
    -------------------------------------------------------------------------------------
    | Id  | Operation            | Name | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      | 10000 |    97K|       |  2515   (1)| 00:00:31 |
    |*  1 |  HASH JOIN RIGHT ANTI|      | 10000 |    97K|    16M|  2515   (1)| 00:00:31 |
    |   2 |   TABLE ACCESS FULL  | B    |   999K|  4882K|       |   448   (2)| 00:00:06 |
    |   3 |   TABLE ACCESS FULL  | A    |  1000K|  4882K|       |   448   (2)| 00:00:06 |
    -------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("B"."ID"="A"."ID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              2  db block gets
           3210  consistent gets
              0  physical reads
              0  redo SIZE
           2564  bytes sent via SQL*Net TO client
            596  bytes received via SQL*Net FROM client
              9  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
            107  ROWS processed


    SQL>
    SQL> SELECT id
      2  FROM a
      3  WHERE id NOT IN (SELECT id FROM b);


            ID
    ----------
          9317
         18634
         27951
         37268
         46585
         55902
         65219
         74536
        158389
        149072
        139755


            ID
    ----------
        130438
        121121
         83853
        111804
        102487
         93170
        232925
        223608
        242242
        214291
        204974


            ID
    ----------
        167706
        195657
        186340
        177023
        326095
        316778
        307461
        298144
        288827
        260876
        251559


            ID
    ----------
        279510
        270193
        409948
        391314
        400631
        381997
        372680
        344729
        335412
        363363
        354046


            ID
    ----------
        475167
        484484
        465850
        456533
        437899
        428582
        419265
        447216
        559020
        568337
        549703


            ID
    ----------
        540386
        521752
        512435
        503118
        493801
        531069
        652190
        642873
        633556
        624239
        614922


            ID
    ----------
        605605
        596288
        586971
        577654
        736043
        726726
        717409
        708092
        698775
        689458
        680141


            ID
    ----------
        670824
        661507
        791945
        819896
        810579
        801262
        782628
        773311
        763994
        754677
        745360


            ID
    ----------
        885115
        875798
        903749
        894432
        866481
        857164
        847847
        838530
        829213
        978285
        968968


            ID
    ----------
        959651
        987602
        950334
        941017
        931700
        922383
        913066
        996919


    107 ROWS selected.


    Elapsed: 00:00:00.53


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 548673372
    ----------------------------------------------------------------------------------------
    | Id  | Operation               | Name | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |      | 10000 |    97K|       |  2515   (1)| 00:00:31 |
    |*  1 |  HASH JOIN RIGHT ANTI NA|      | 10000 |    97K|    16M|  2515   (1)| 00:00:31 |
    |   2 |   TABLE ACCESS FULL     | B    |   999K|  4882K|       |   448   (2)| 00:00:06 |
    |   3 |   TABLE ACCESS FULL     | A    |  1000K|  4882K|       |   448   (2)| 00:00:06 |
    ----------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("ID"="ID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              2  db block gets
           3210  consistent gets
              0  physical reads
              0  redo SIZE
           2564  bytes sent via SQL*Net TO client
            596  bytes received via SQL*Net FROM client
              9  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
            107  ROWS processed


    SQL>
    SQL> SELECT id
      2  FROM a
      3  minus
      4  SELECT id
      5  FROM b;


            ID
    ----------
          9317
         18634
         27951
         37268
         46585
         55902
         65219
         74536
         83853
         93170
        102487


            ID
    ----------
        111804
        121121
        130438
        139755
        149072
        158389
        167706
        177023
        186340
        195657
        204974


            ID
    ----------
        214291
        223608
        232925
        242242
        251559
        260876
        270193
        279510
        288827
        298144
        307461


            ID
    ----------
        316778
        326095
        335412
        344729
        354046
        363363
        372680
        381997
        391314
        400631
        409948


            ID
    ----------
        419265
        428582
        437899
        447216
        456533
        465850
        475167
        484484
        493801
        503118
        512435


            ID
    ----------
        521752
        531069
        540386
        549703
        559020
        568337
        577654
        586971
        596288
        605605
        614922


            ID
    ----------
        624239
        633556
        642873
        652190
        661507
        670824
        680141
        689458
        698775
        708092
        717409


            ID
    ----------
        726726
        736043
        745360
        754677
        763994
        773311
        782628
        791945
        801262
        810579
        819896


            ID
    ----------
        829213
        838530
        847847
        857164
        866481
        875798
        885115
        894432
        903749
        913066
        922383


            ID
    ----------
        931700
        941017
        950334
        959651
        968968
        978285
        987602
        996919


    107 ROWS selected.


    Elapsed: 00:00:01.31


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1035326697
    ------------------------------------------------------------------------------------
    | Id  | Operation           | Name | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |  1000K|  9765K|       |  6997  (51)| 00:01:24 |
    |   1 |  MINUS              |      |       |       |       |            |          |
    |   2 |   SORT UNIQUE       |      |  1000K|  4882K|    11M|  3498   (1)| 00:00:42 |
    |   3 |    TABLE ACCESS FULL| A    |  1000K|  4882K|       |   448   (2)| 00:00:06 |
    |   4 |   SORT UNIQUE       |      |   999K|  4882K|    11M|  3498   (1)| 00:00:42 |
    |   5 |    TABLE ACCESS FULL| B    |   999K|  4882K|       |   448   (2)| 00:00:06 |
    ------------------------------------------------------------------------------------

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              2  db block gets
           3198  consistent gets
              0  physical reads
              0  redo SIZE
           2564  bytes sent via SQL*Net TO client
            596  bytes received via SQL*Net FROM client
              9  SQL*Net roundtrips TO/FROM client
              2  sorts (memory)
              0  sorts (disk)
            107  ROWS processed


    SQL>
     

    Notice the plans didn't change after the indexes were created; there was no need to use them since Oracle was selecting all rows from both tables. Of course this isn't to say an index won't be beneficial, it can be, if it's created on the right column or set of columns:


    Code (SQL):

    SQL> CREATE TABLE a (
      2          id NUMBER,
      3          dt      DATE
      4  );


    TABLE created.


    SQL>
    SQL> CREATE TABLE b (
      2          id NUMBER,
      3          dt      DATE
      4  );


    TABLE created.


    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000000 loop
      3                  INSERT INTO a
      4                  VALUES(i, trunc(sysdate)+i);
      5                  IF MOD(i,9317) <> 0 THEN
      6                          INSERT INTO b
      7                          VALUES(i, trunc(sysdate)+i);
      8                  END IF;
      9          END loop;
     10
     11          commit;
     12
     13  END;
     14  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> EXEC dbms_stats.gather_schema_stats('GRIBNAUT')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> CREATE INDEX a_id_idx ON a(dt);


    INDEX created.


    SQL> CREATE INDEX b_id_idx ON b(dt);


    INDEX created.


    SQL>
    SQL> SET autotrace ON timing ON
    SQL>
    SQL> SELECT a.id
      2  FROM a LEFT JOIN b
      3  ON b.id = a.id
      4  WHERE a.dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000
      5  AND b.dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000
      6  AND b.id IS NULL;


    no ROWS selected


    Elapsed: 00:00:00.04


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 991751610
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |     1 |    26 |    97   (0)| 00:00:02 |
    |*  1 |  FILTER                        |          |       |       |            |          |
    |   2 |   NESTED LOOPS                 |          |       |       |            |          |
    |   3 |    NESTED LOOPS                |          |     1 |    26 |    97   (0)| 00:00:02 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID| B        |     1 |    13 |    49   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN           | A_ID_IDX |  9002 |       |    25   (0)| 00:00:01 |
    |*  7 |    TABLE ACCESS BY INDEX ROWID | A        |     1 |    13 |    48   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       4 - FILTER("B"."ID" IS NULL)
       5 - access("B"."DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "B"."DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])
       6 - access("A"."DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "A"."DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])
       7 - FILTER("B"."ID"="A"."ID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             49  consistent gets
             26  physical reads
              0  redo SIZE
            330  bytes sent via SQL*Net TO client
            509  bytes received via SQL*Net FROM client
              1  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              0  ROWS processed


    SQL>
    SQL> SELECT a.id
      2  FROM a, b
      3  WHERE a.dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000
      4  AND b.dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000
      5  AND b.id (+) = a.id
      6  AND b.id IS NULL;


    no ROWS selected


    Elapsed: 00:00:00.00


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 991751610
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |     1 |    26 |    97   (0)| 00:00:02 |
    |*  1 |  FILTER                        |          |       |       |            |          |
    |   2 |   NESTED LOOPS                 |          |       |       |            |          |
    |   3 |    NESTED LOOPS                |          |     1 |    26 |    97   (0)| 00:00:02 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID| B        |     1 |    13 |    49   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN           | A_ID_IDX |  9002 |       |    25   (0)| 00:00:01 |
    |*  7 |    TABLE ACCESS BY INDEX ROWID | A        |     1 |    13 |    48   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       4 - FILTER("B"."ID" IS NULL)
       5 - access("B"."DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "B"."DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])
       6 - access("A"."DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "A"."DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])
       7 - FILTER("B"."ID"="A"."ID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             49  consistent gets
              0  physical reads
              0  redo SIZE
            330  bytes sent via SQL*Net TO client
            509  bytes received via SQL*Net FROM client
              1  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              0  ROWS processed


    SQL>
    SQL> SELECT id
      2  FROM
      3  (SELECT a.id, a.dt
      4   FROM a LEFT JOIN b
      5   ON b.id = a.id
      6   WHERE b.id IS NULL)
      7  WHERE dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000;


            ID
    ----------
          9317


    Elapsed: 00:00:00.21


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 764351325
    ------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |          |    90 |  1620 |   740   (2)| 00:00:09 |
    |*  1 |  FILTER                       |          |       |       |            |          |
    |*  2 |   HASH JOIN ANTI              |          |    90 |  1620 |   740   (2)| 00:00:09 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
    |   5 |    TABLE ACCESS FULL          | B        |   999K|  4882K|   687   (1)| 00:00:09 |
    ------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       2 - access("B"."ID"="A"."ID")
       4 - access("A"."DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND
                  "A"."DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              1  db block gets
           2607  consistent gets
             26  physical reads
              0  redo SIZE
            521  bytes sent via SQL*Net TO client
            520  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed


    SQL>
    SQL> SELECT a_id
      2  FROM
      3  (SELECT id a_id
      4  FROM a
      5  WHERE dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000) a,
      6  (SELECT id b_id
      7  FROM b
      8  WHERE dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000) b
      9  WHERE b_id (+) = a_id
     10  AND b_id IS NULL;


          A_ID
    ----------
          9317


    Elapsed: 00:00:01.42


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 4150175149
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |    90 |  2340 |    99   (2)| 00:00:02 |
    |*  1 |  FILTER                        |          |       |       |            |          |
    |*  2 |   FILTER                       |          |       |       |            |          |
    |*  3 |    HASH JOIN OUTER             |          |    90 |  2340 |    99   (2)| 00:00:02 |
    |   4 |     TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
    |   6 |     TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
    |*  7 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       2 - FILTER("ID" IS NULL)
       3 - access("ID"(+)="ID")
       5 - access("DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])
       7 - access("DT"(+)>=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "DT"(+)<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             98  consistent gets
             26  physical reads
              0  redo SIZE
            523  bytes sent via SQL*Net TO client
            520  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed


    SQL>
    SQL> SELECT id
      2  FROM a
      3  WHERE id NOT IN (SELECT id FROM b WHERE dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+1000
      4  AND a.dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000;


            ID
    ----------
          9317


    Elapsed: 00:00:00.01


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2827420310
    ------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |          |    90 |  2340 |    99   (2)| 00:00:02 |
    |*  1 |  FILTER                       |          |       |       |            |          |
    |*  2 |   HASH JOIN ANTI NA           |          |    90 |  2340 |    99   (2)| 00:00:02 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
    |   5 |    TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       2 - access("ID"="ID")
       4 - access("A"."DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND
                  "A"."DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])
       6 - access("DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             98  consistent gets
              0  physical reads
              0  redo SIZE
            521  bytes sent via SQL*Net TO client
            520  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed


    SQL>
    SQL> SELECT id
      2  FROM a
      3  WHERE dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000
      4  minus
      5  SELECT id
      6  FROM b
      7  WHERE dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000;


            ID
    ----------
          9317


    Elapsed: 00:00:00.01


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3815546394
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |  9002 |   228K|   101  (52)| 00:00:02 |
    |   1 |  MINUS                         |          |       |       |            |          |
    |   2 |   SORT UNIQUE                  |          |  9002 |   114K|    50   (2)| 00:00:01 |
    |*  3 |    FILTER                      |          |       |       |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
    |   6 |   SORT UNIQUE                  |          |  9001 |   114K|    50   (2)| 00:00:01 |
    |*  7 |    FILTER                      |          |       |       |            |          |
    |   8 |     TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
    |*  9 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       3 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       5 - access("DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])
       7 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       9 - access("DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND "DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             98  consistent gets
              0  physical reads
              0  redo SIZE
            521  bytes sent via SQL*Net TO client
            520  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              2  sorts (memory)
              0  sorts (disk)
              1  ROWS processed


    SQL>
     

    Note that indexes are now used on these queries, reducing the amount of data Oracle must process to return the desired results. Notice also that the first two queries return no rows, which is incorrect. The third query, a slight re-write of the original outer join, returns the correct result, as does every query afterwards. Attempting to 'window' the data in both tables in an outer join fails; the original query was modified to include a.dt and then used as an in-line view to allow Oracle to return the one value missing in table B for that date range. When the left outer join is allowed to run minus any additional restrictions, returning the full result set, then that result is filtered on the dt values the correct answer, 9317, is displayed. Modifying the query yet again, to 'window' only the data in table A, returns the correct result:


    Code (SQL):

    SQL> SELECT a.id
      2  FROM a LEFT JOIN b
      3  ON b.id = a.id
      4  WHERE a.dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000
      5  AND b.id IS NULL;


            ID
    ----------
          9317


    Elapsed: 00:00:01.69


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 764351325
    ------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |          |    90 |  1620 |   740   (2)| 00:00:09 |
    |*  1 |  FILTER                       |          |       |       |            |          |
    |*  2 |   HASH JOIN ANTI              |          |    90 |  1620 |   740   (2)| 00:00:09 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
    |   5 |    TABLE ACCESS FULL          | B        |   999K|  4882K|   687   (1)| 00:00:09 |
    ------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       2 - access("B"."ID"="A"."ID")
       4 - access("A"."DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND
                  "A"."DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              1  db block gets
           2606  consistent gets
             26  physical reads
              0  redo SIZE
            521  bytes sent via SQL*Net TO client
            519  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed


    SQL>
    SQL> SELECT a.id
      2  FROM a, b
      3  WHERE a.dt BETWEEN trunc(sysdate)+1000 AND trunc(sysdate)+10000
      4  AND b.id (+) = a.id
      5  AND b.id IS NULL;


            ID
    ----------
          9317


    Elapsed: 00:00:00.07


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 764351325
    ------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |          |    90 |  1620 |   740   (2)| 00:00:09 |
    |*  1 |  FILTER                       |          |       |       |            |          |
    |*  2 |   HASH JOIN ANTI              |          |    90 |  1620 |   740   (2)| 00:00:09 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
    |   5 |    TABLE ACCESS FULL          | B        |   999K|  4882K|   687   (1)| 00:00:09 |
    ------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER(TRUNC([EMAIL="SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000"]SYSDATE@!)+1000<=TRUNC(SYSDATE@!)+10000[/EMAIL])
       2 - access("B"."ID"="A"."ID")
       4 - access("A"."DT">=TRUNC([EMAIL="SYSDATE@!)+1000"]SYSDATE@!)+1000[/EMAIL] AND
                  "A"."DT"<=TRUNC([EMAIL="SYSDATE@!)+10000"]SYSDATE@!)+10000[/EMAIL])

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              1  db block gets
           2606  consistent gets
              0  physical reads
              0  redo SIZE
            521  bytes sent via SQL*Net TO client
            519  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              1  ROWS processed


    SQL>
     

    It doesn't work when both tables are 'windowed', again something I didn't expect. I will investigate further to find out why Oracle displays this behavior.


    We took a bit further trip than expected given the unexpected behavior of outer joins modified to process a reduced window of values; I hope this isn't confusing to you. The initial aim was to show that indexes can help in such tasks provided the RIGHT indexes are created, and that was successful. A side benefit from this was learning how to modify outer joins to return correct results when a partial set of the whole is used for comparison.
     
    OldSchoolCoder likes this.