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!

plz help to understand hw the right outer join works for multiple tables

Discussion in 'SQL PL/SQL' started by shubhangi, Aug 20, 2012.

  1. shubhangi

    shubhangi Guest

    plz help to understand hw the right outer join works for multiple tables

    when i m trying to copy my query here getting error
    1.Oops! your Post seems to be a SPAM! If you are posting live links, you need to have made at least 5 Posts as required by the administrator. Please edit your post or contact the administrator.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A join involves two tables at a time and there can be many tables in a query which can be joined together, essentially. These joins, again, occur in pairs and can involve actual tables or in-line views (which can contain more than one table). Yes, it can be confusing however look at a simple example:

    Code (SQL):
    SQL> --
    SQL> -- Create test tables with keys and indexes
    SQL> --
    SQL> CREATE TABLE t1(
      2        col1 NUMBER,
      3        col2 varchar2(40),
      4        col_r NUMBER NOT NULL,
      5        CONSTRAINT t1_pk
      6        PRIMARY KEY(col1));
     
    TABLE created.
     
    Elapsed: 00:00:00.04

    SQL>
    SQL> CREATE TABLE t2(
      2        col1_2 NUMBER NOT NULL,
      3        col2_2 NUMBER NOT NULL,
      4        col3_2 varchar2(40),
      5        colr_2 NUMBER NOT NULL,
      6        CONSTRAINT t1_t2_fk FOREIGN KEY (col1_2) REFERENCES t1(col1));
     
    TABLE created.
     
    Elapsed: 00:00:00.01

    SQL>
    SQL> CREATE INDEX t2_fk ON t2(col1_2);
     
    INDEX created.
     
    Elapsed: 00:00:00.00

    SQL> CREATE UNIQUE INDEX t2_uq ON t2(col2_2, colr_2);
     
    INDEX created.
     
    Elapsed: 00:00:00.01

    SQL>
    SQL> --
    SQL> -- Populate tables
    SQL> --
    SQL> -- Table T2 will have missing records
    SQL> --
    SQL> BEGIN
      2        FOR b IN 1..100000 loop
      3         INSERT INTO t1 VALUES(b, 'Test value '||b, MOD(b, 17));
      4         IF MOD(b,443) <> 0 THEN
      5          INSERT INTO t2 VALUES(b, b, 'Test value '||b, MOD(b,17));
      6         END IF;
      7        END loop;
      8  
      9        commit;
     10  END;
     11  /
     
    PL/SQL PROCEDURE successfully completed.
     
    Elapsed: 00:00:18.67

    SQL>
    SQL> --
    SQL> -- Generate schema stats
    SQL> --
    SQL> EXEC dbms_stats.gather_schema_stats(ownname=>'BING', estimate_percent=>NULL)
     
    PL/SQL PROCEDURE successfully completed.
     
    Elapsed: 00:00:03.34

    SQL>
    SQL> --
    SQL> -- Find the missing records and check execution plans
    SQL> --
    SQL> SET autotrace ON
    SQL> ALTER SESSION SET optimizer_features_enable='10.2.0.5';
     
    SESSION altered.
     
    Elapsed: 00:00:00.01

    SQL>
    SQL> --
    SQL> -- This will return all PK values not found in T2
    SQL> --
    SQL> -- Notice that the column where values are missing
    SQL> -- is NULL
    SQL>
    SQL> SELECT col1, col1_2
      2  FROM t1 LEFT OUTER JOIN t2 ON (col1_2 = col1)
      3  WHERE col1_2 IS NULL;
     
          COL1     COL1_2
    ---------- ----------
           443
           886
          1329
          1772
          2215
          2658
          3101
          3544
          3987
          4430
          4873
     
          COL1     COL1_2
    ---------- ----------
          5316
          5759
          6202
          6645
          7088
          7531
          7974
          8417
          8860
          9303
          9746
     
          COL1     COL1_2
    ---------- ----------
         10189
         10632
         11075
         11518
         11961
         12404
         12847
         13290
         13733
         14176
         14619
     
          COL1     COL1_2
    ---------- ----------
         15062
         15505
         15948
         16391
         16834
         17277
         17720
         18163
         18606
         19049
         19492
     
          COL1     COL1_2
    ---------- ----------
         19935
         20378
         20821
         21264
         21707
         22150
         22593
         23036
         23479
         23922
         24365
     
          COL1     COL1_2
    ---------- ----------
         24808
         25251
         25694
         26137
         26580
         27023
         27466
         27909
         28352
         28795
         29238
     
          COL1     COL1_2
    ---------- ----------
         29681
         30124
         30567
         31010
         31453
         31896
         32339
         32782
         33225
         33668
         34111
     
          COL1     COL1_2
    ---------- ----------
         34554
         34997
         35440
         35883
         36326
         36769
         37212
         37655
         38098
         38541
         38984
     
          COL1     COL1_2
    ---------- ----------
         39427
         39870
         40313
         40756
         41199
         41642
         42085
         42528
         42971
         43414
         43857
     
          COL1     COL1_2
    ---------- ----------
         44300
         44743
         45186
         45629
         46072
         46515
         46958
         47401
         47844
         48287
         48730
     
          COL1     COL1_2
    ---------- ----------
         49173
         49616
         50059
         50502
         50945
         51388
         51831
         52274
         52717
         53160
         53603
     
          COL1     COL1_2
    ---------- ----------
         54046
         54489
         54932
         55375
         55818
         56261
         56704
         57147
         57590
         58033
         58476
     
          COL1     COL1_2
    ---------- ----------
         58919
         59362
         59805
         60248
         60691
         61134
         61577
         62020
         62463
         62906
         63349
     
          COL1     COL1_2
    ---------- ----------
         63792
         64235
         64678
         65121
         65564
         66007
         66450
         66893
         67336
         67779
         68222
     
          COL1     COL1_2
    ---------- ----------
         68665
         69108
         69551
         69994
         70437
         70880
         71323
         71766
         72209
         72652
         73095
     
          COL1     COL1_2
    ---------- ----------
         73538
         73981
         74424
         74867
         75310
         75753
         76196
         76639
         77082
         77525
         77968
     
          COL1     COL1_2
    ---------- ----------
         78411
         78854
         79297
         79740
         80183
         80626
         81069
         81512
         81955
         82398
         82841
     
          COL1     COL1_2
    ---------- ----------
         83284
         83727
         84170
         84613
         85056
         85499
         85942
         86385
         86828
         87271
         87714
     
          COL1     COL1_2
    ---------- ----------
         88157
         88600
         89043
         89486
         89929
         90372
         90815
         91258
         91701
         92144
         92587
     
          COL1     COL1_2
    ---------- ----------
         93030
         93473
         93916
         94359
         94802
         95245
         95688
         96131
         96574
         97017
         97460
     
          COL1     COL1_2
    ---------- ----------
         97903
         98346
         98789
         99232
         99675
     
    225 ROWS selected.
     
    Elapsed: 00:00:01.14
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 4142031255
    -----------------------------------------------------------------------------
    | Id  | Operation           | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |       |     1 |    10 |    39   (0)| 00:00:01 |
    |*  1 |  FILTER             |       |       |       |            |          |
    |   2 |   NESTED LOOPS OUTER|       |     1 |    10 |    39   (0)| 00:00:01 |
    |   3 |    INDEX FULL SCAN  | T1_PK |   100K|   488K|    38   (0)| 00:00:01 |
    |*  4 |    INDEX RANGE SCAN | T2_FK |     1 |     5 |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("T2"."COL1_2" IS NULL)
       4 - access("COL1_2"(+)="COL1")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           1629  consistent gets
              0  physical reads
              0  redo SIZE
           4506  bytes sent via SQL*Net TO client
            574  bytes received via SQL*Net FROM client
             16  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
            225  ROWS processed
     
    SQL>
    SQL>
    SQL> --
    SQL> -- This returns no rows since a PK cannot be NULL
    SQL> --
    SQL> -- Again the column where values are missing will
    SQL> -- be NULL for an outer join
    SQL>
    SQL> SELECT col1, col1_2
      2  FROM t1 RIGHT OUTER JOIN t2 ON (col1 = col1_2)
      3  WHERE col1 IS NULL;
     
    no ROWS selected
     
    Elapsed: 00:00:01.14
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 742290553
    -----------------------------------------------------------------------------
    | Id  | Operation           | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |       |     1 |    10 |    42   (3)| 00:00:01 |
    |*  1 |  FILTER             |       |       |       |            |          |
    |   2 |   NESTED LOOPS OUTER|       |     1 |    10 |    42   (3)| 00:00:01 |
    |   3 |    INDEX FULL SCAN  | T2_FK | 99775 |   487K|    40   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN| T1_PK |     1 |     5 |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("T1"."COL1" IS NULL)
       4 - access("COL1"(+)="COL1_2")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           1511  consistent gets
              0  physical reads
              0  redo SIZE
            344  bytes sent via SQL*Net TO client
            409  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> SET autotrace off
    SQL>
     
    With an outer join any missing data is displayed (if you will) as NULL; a left outer join will display NULLs from data on the 'left' table in the join syntax and a right outer join will return NULLs on the 'right' table of the join (see the example above for how results are displayed). The right outer join in the example shown returns no rows because a primary key column cannot be NULL thus there can be no 'missing' values between pk and fk columns.