• Status: Solved
• Priority: Medium
• Security: Public
• Views: 274

# 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 (=)?
0
soccerplayer
1 Solution

Commented:
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

Author 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

Commented:
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'
``````

this produces...

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

2 rows selected.
``````

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

Commented:
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';
``````

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

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

Author 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

Commented:
no

look at my examples again and descriptions again

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

NOT AFTER

AND Tbl2.ColFlg='A'

this is AFTER and hence different results
0

Author 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

Commented:
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

Commented:
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
;
``````
``````**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 |
``````
``````**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 |
``````
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) |
``````
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.