# Thread: WHERE TABLE1=TABLE2(+) - 10g

1. Registered User
Join Date
Aug 2008
Posts
464

## Unanswered: WHERE TABLE1=TABLE2(+) - 10g

Hi guys

Probably very basic for you guys but I couldn't find anything useful on the Internet.

I have a query which selects data from 2 tables with aliases A and B.

When I use the condition:

Code:
```SELECT A.DATETIME,A.COL1,A.COL2, (A.COL3+B.COL4) ABC
FROM TABLE1 A, TABLE2 B
WHERE A.DATETIME BETWEEN TRUNC(SYSDATE)-1 and TRUNC(SYSDATE)
AND A.DATETIME = B.DATETIME (+) AND A.COL1 = B. COL1(+) AND A.COL3 = B.COL3 (+)
GROUP BY A.DATETIME,A.COL1,A.COL2```
I get all zeros.

but when I use:

Code:
```SELECT B.DATETIME,B.COL1,B.COL2, (A.COL3+B.COL4) ABC
FROM TABLE1 A, TABLE2 B
WHERE A.DATETIME BETWEEN TRUNC(SYSDATE)-1 and TRUNC(SYSDATE)
AND A.DATETIME(+) = B.DATETIME  AND A.COL1(+) = B. COL1 AND A.COL3(+) = B.COL3
GROUP BY B.DATETIME,B.COL1,B.COL2```
I get the right values.

I'd appreciate if someone could help me understand how data is being selected in both cases?

Note: The first query is supposed to return the correct results instead of zeros.

Regards
Shajju
Last edited by shajju; 01-30-14 at 02:30.

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,112
Provided Answers: 5
The first query is supposed to return the correct results
How do you know? I mean, if it turns out that the second query returns correct result, then, hm? Anyway: use Google and read about outer joins.

3. Registered User
Join Date
Aug 2008
Posts
464
Thanks.

Sorry the comparison should be:

Code:
`AND A.DATETIME = B.DATETIME (+) AND A.COL1 = B. COL1(+) AND A.COL2 = B.COL2 (+)`
instead of

Code:
`AND A.DATETIME = B.DATETIME (+) AND A.COL1 = B. COL1(+) AND A.COL3 = B.COL3 (+)`
The first query returns proper results for all days of this month apart from 2. For those 2 days, I get zeros.

I will read up on Outer Joins but could you explain in ur own words too please?

4. Registered User
Join Date
Nov 2003
Posts
2,985
Provided Answers: 23
Please stop using the old deprecated Oracle specific outer join syntax. Learn to use explicit `LEFT JOIN`s in the from clause

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•