What does this join mean?

Most likely, this join condition is used in conjunction with other outer joins.

Outer joins can be best explained by example.

http://www.programmerinterview.com/index.php/database-sql/difference-between-a-left-outer-join-and-right-outer-join/

ajexpert, thank you for your reponse.

Here is the complete FROM and WHERE clause for the SQL:

FROM Tbl1, Tbl2, Tbl3
WHERE
Tbl3.ColID=Tbl2.ColID(+)
AND Tbl3.ColDate=Tbl1.ColDate
AND Tbl2.ColFlg(+)='A'
AND Tbl3.ColFlg2='B'

I am very familiar with all kinds of outer joins. I saw this code and it didn't make sense to me. Can you explain with this info?

Thanks.

This is one of the advantages of using ANSI join syntax, you can clearly delineate which conditions apply as part of the join and which are after the join


this produces 2 rows of output, as the example above

WITH tbl2
     AS (SELECT 1 colid, 'A' colflg FROM DUAL
         UNION ALL
         SELECT 1, 'X' FROM DUAL),
     tbl3
     AS (SELECT 1 colid, 'B' colflg2 FROM DUAL
         UNION ALL
         SELECT 2, 'B' FROM DUAL)
SELECT *
  FROM tbl2 RIGHT OUTER JOIN tbl3 ON tbl3.colid = tbl2.colid AND tbl2.colflg = 'A'
 WHERE tbl3.colflg2 = 'B';

Select allOpen in new window

this one only produces 1 row of output

WITH tbl2
     AS (SELECT 1 colid, 'A' colflg FROM DUAL
         UNION ALL
         SELECT 1, 'X' FROM DUAL),
     tbl3
     AS (SELECT 1 colid, 'B' colflg2 FROM DUAL
         UNION ALL
         SELECT 2, 'B' FROM DUAL)
SELECT *
  FROM tbl2 RIGHT OUTER JOIN tbl3 ON tbl3.colid = tbl2.colid
 WHERE tbl3.colflg2 = 'B' AND tbl2.colflg = 'A';

Select allOpen in new window

you could specify the tbl3.colflg2 ='B' condition in either the ON or the WHERE without a difference in either case

I understand that this is a filter after the join.

Still I don't understand why we don't do this:

FROM Tbl1, Tbl2, Tbl3
WHERE
Tbl3.ColID=Tbl2.ColID(+)
AND Tbl3.ColDate=Tbl1.ColDate
AND Tbl2.ColFlg='A'
AND Tbl3.ColFlg2='B'

The rows returned will be exactly the same, in your example, just 1.

no

look at my examples again and descriptions again



the  AND Tbl2.ColFlg(+)='A'  condition will apply AS PART of the join


NOT AFTER

Your code in your  last example...

AND Tbl2.ColFlg='A'

this is AFTER and hence different results

stdstuber, thank you for your response. I am sure you are right, it is just that I am having a hard time understanding this.

So in this code:

SELECT *
  FROM tbl2 RIGHT OUTER JOIN tbl3 ON tbl3.colid = tbl2.colid AND tbl2.colflg = 'A'
 WHERE tbl3.colflg2 = 'B';

Oracle does two join filters simultaneously and still gets two rows. I would think that 2nd part of the join would bring down the two rows to one row. This is what I don't understand.

You can run my examples,  and play around with the WITH clause to change inputs and see how the various join methods change the output.

the A filter on tbl2 prevents the X row from being joined to either row of tbl3

But, it's an OUTER join,  so....

The colid=1 join finds the tbl2 A and joins to it.
The colid=2 join finds no row in tbl2 and thus outer joins with null values.


If you put the  tbl2.colflg='A' condition in the ANSI join WHERE clause then
the same conditions apply as before except the NULL rows will fail the where condition because NULL ='A' is false.

Similarly, if you use the non-ansi join syntax with   tbl2.colflg='A' without the (+) then that's the same thing, the condition will be applied after the (+) outer joins and thus cause the NULL rows to be excluded

FROM Tbl1, Tbl2, Tbl3
WHERE
Tbl3.ColID=Tbl2.ColID(+)              
AND Tbl3.ColDate=Tbl1.ColDate
AND Tbl2.ColFlg(+)='A'
AND Tbl3.ColFlg2='B'

I have trouble thinking in '(+)' terms these days, so I apologize in advance if this is a confusion of facts.

It seems to me that for that given query, that the join between Tbl3 and Tbl1 is going to suppress the effect of those '(+)' instructions.

But unless there is some relevant sample data, and an understanding of what the intention behind the original query is, we are left to ponder. With this assumed data:

    CREATE TABLE Tbl1
    	("T1_STR" varchar2(6), "COLDATE" timestamp)
    ;
    
    INSERT ALL 
    	INTO Tbl1 ("T1_STR", "COLDATE")
    		 VALUES ('table1', '01-Jan-2013 12:00:00 AM')
    	INTO Tbl1 ("T1_STR", "COLDATE")
    		 VALUES ('table1', '01-Feb-2013 12:00:00 AM')
    	INTO Tbl1 ("T1_STR", "COLDATE")
    		 VALUES ('table1', '01-Feb-2000 12:00:00 AM') --<< unmatched
    SELECT * FROM dual
    ;
    
    CREATE TABLE Tbl2
    	("COLID" int, "T2_STR" varchar2(6), "COLFLG" varchar2(1))
    ;
    
    INSERT ALL 
    	INTO Tbl2 ("COLID", "T2_STR", "COLFLG")
    		 VALUES (1, 'table2', 'A')
    	INTO Tbl2 ("COLID", "T2_STR", "COLFLG")
    		 VALUES (2, 'table2', 'A')
    	INTO Tbl2 ("COLID", "T2_STR", "COLFLG")
    		 VALUES (3, 'table2', 'A')
    	INTO Tbl2 ("COLID", "T2_STR", "COLFLG")
    		 VALUES (4, 'table2', 'X')                 --<< unmatched
    SELECT * FROM dual
    ;
    
    
    CREATE TABLE Tbl3
    	("COLID" int, "T3_STR" varchar2(6), "COLFLG2" varchar2(1), "COLDATE" timestamp)
    ;
    
    INSERT ALL 
    	INTO Tbl3 ("COLID", "T3_STR", "COLFLG2", "COLDATE")
    		 VALUES (1, 'table3', 'B', '01-Jan-2013 12:00:00 AM')
    	INTO Tbl3 ("COLID", "T3_STR", "COLFLG2", "COLDATE")
    		 VALUES (2, 'table3', 'B', '01-Feb-2013 12:00:00 AM')
    	INTO Tbl3 ("COLID", "T3_STR", "COLFLG2", "COLDATE")
    		 VALUES (3, 'table3', 'C', '01-Mar-2013 12:00:00 AM') --<< unmatched
    SELECT * FROM dual
    ;

Select allOpen in new window

**Query 1**:
    SELECT
      'as is' as t, tbl2.t2_str, tbl2.colid, tbl2.colflg, tbl3.t3_str, tbl3.colflg2, tbl3.coldate, tbl1.t1_str
    FROM Tbl1, Tbl2, Tbl3
    WHERE
    Tbl3.ColID=Tbl2.ColID(+)
    AND Tbl3.ColDate=Tbl1.ColDate
    AND Tbl2.ColFlg(+)='A'
    AND Tbl3.ColFlg2='B'

    **[Results]**:
    
    |     T | T2_STR | COLID | COLFLG | T3_STR | COLFLG2 |                         COLDATE | T1_STR |
    |-------|--------|-------|--------|--------|---------|---------------------------------|--------|
    | as is | table2 |     1 |      A | table3 |       B |  January, 01 2013 00:00:00+0000 | table1 |
    | as is | table2 |     2 |      A | table3 |       B | February, 01 2013 00:00:00+0000 | table1 |

**Query 2**:

    SELECT
      'no (+)' as t, tbl2.t2_str, tbl2.colid, tbl2.colflg, tbl3.t3_str, tbl3.colflg2, tbl3.coldate, tbl1.t1_str
    FROM Tbl1, Tbl2, Tbl3
    WHERE
    Tbl3.ColID=Tbl2.ColID
    AND Tbl3.ColDate=Tbl1.ColDate
    AND Tbl2.ColFlg='A'
    AND Tbl3.ColFlg2='B'

**[Results]**:
    
    |      T | T2_STR | COLID | COLFLG | T3_STR | COLFLG2 |                         COLDATE | T1_STR |
    |--------|--------|-------|--------|--------|---------|---------------------------------|--------|
    | no (+) | table2 |     1 |      A | table3 |       B |  January, 01 2013 00:00:00+0000 | table1 |
    | no (+) | table2 |     2 |      A | table3 |       B | February, 01 2013 00:00:00+0000 | table1 |

Select allOpen in new window

**Query 3**:

    SELECT
      'extra (+)' as t, tbl2.t2_str, tbl2.colid, tbl2.colflg, tbl3.t3_str, tbl3.colflg2, tbl3.coldate, tbl1.t1_str
    FROM Tbl1, Tbl2, Tbl3
    WHERE
    Tbl3.ColID=Tbl2.ColID(+)
    AND Tbl3.ColDate(+)=Tbl1.ColDate
    AND Tbl2.ColFlg(+)='A'
    AND Tbl3.ColFlg2(+)='B'

**[Results]**:
    
    |         T | T2_STR |  COLID | COLFLG | T3_STR | COLFLG2 |                         COLDATE | T1_STR |
    |-----------|--------|--------|--------|--------|---------|---------------------------------|--------|
    | extra (+) | table2 |      1 |      A | table3 |       B |  January, 01 2013 00:00:00+0000 | table1 |
    | extra (+) | table2 |      2 |      A | table3 |       B | February, 01 2013 00:00:00+0000 | table1 |
    | extra (+) | (null) | (null) | (null) | (null) |  (null) |                          (null) | table1 |

Select allOpen in new window

There are a variety of other variants one could try (see: attachment ) - but I'm left with the impression that using a RIGHT OUTER (either ansi or not) is just a confusion anyway and that the sequence of tables should be changed so that LEFT JOINS are used (if an outer join is needed), or that indeed inner joins are used.

**Query 8**:

    SELECT
      'ANSI LEFTs' as t, tbl2.t2_str, tbl2.colid, tbl2.colflg, tbl3.t3_str, tbl3.colflg2, tbl3.coldate, tbl1.t1_str
    FROM Tbl2
    LEFT OUTER JOIN Tbl3 ON Tbl3.ColID = Tbl2.ColID   -- (+)
                        AND Tbl3.ColFlg2='B'
    LEFT OUTER JOIN Tbl1 ON Tbl3.ColDate = Tbl1.ColDate
    WHERE Tbl2.ColFlg='A'          -- (+)

**[Results]**:
    
    |          T | T2_STR | COLID | COLFLG | T3_STR | COLFLG2 |                         COLDATE | T1_STR |
    |------------|--------|-------|--------|--------|---------|---------------------------------|--------|
    | ANSI LEFTs | table2 |     1 |      A | table3 |       B |  January, 01 2013 00:00:00+0000 | table1 |
    | ANSI LEFTs | table2 |     2 |      A | table3 |       B | February, 01 2013 00:00:00+0000 | table1 |
    | ANSI LEFTs | table2 |     3 |      A | (null) |  (null) |                          (null) | (null) |

Select allOpen in new window

To the original question: Looks to me like you are getting the effect of INNER JOINS - but I'd like to know what the query intention is, and sample data.
Q-28246372.txt