What does this join mean?

where
tbl1.col1(+)='A'

This is a right outer join but how are the rows decided? Are only the rows in tbl1.col1 for which the value is 'A' included? If that is the case, why don't we use an inner join (=)?
soccerplayerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ajexpertCommented:
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/
0
soccerplayerAuthor Commented:
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.
0
sdstuberCommented:
The syntax forces an outer join between tbl2 and tbl3

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

perhaps and example would help illustrate
For simplicity, I removed tbl1 as it wasn't pertinent to the join operations here.

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, tbl3
 WHERE tbl3.colid = tbl2.colid(+) 
   AND tbl2.colflg(+) = 'A' 
   AND tbl3.colflg2 = 'B'

Open in new window


this produces...

     COLID COLFLG    COLID_1 COLFLG2
---------- ------ ---------- -------
         1 A               1 B      
                           2 B      

2 rows selected.

Open in new window



Note, the (1,X) row is not included in the join for colid=1


If the AND Tbl2.ColFlg(+)='A' condition were applied after the join, then results would only have a single row: the first one.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
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';

Open 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';

Open in new window



you could specify the tbl3.colflg2 ='B' condition in either the ON or the WHERE without a difference in either case
0
soccerplayerAuthor Commented:
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.
0
sdstuberCommented:
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
0
soccerplayerAuthor Commented:
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.
0
sdstuberCommented:
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
0
PortletPaulfreelancerCommented:
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
    ;

Open 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 |

Open 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 |

Open 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) |

Open 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.