Jayesh Acharya
asked on
where table1.colA(+) <= table2.colB
I am trying to read some sql code that I have been given, but I am having a bit of trouble trying to understand what the following means
where table1.colA(+) <= table2.colB
I think this means the following:
i am doing an outer join of table1
I have all possible data in table 2, but not all data in table 1
So in the where clause
When I have data in both tables and both columns,
the where will check if colA (in table1) is less than or equal to colB (table 2) (and return true or false based on the data in the columns)
When I have no data in Table1 and data in Table2 and ColB
the where will ignore the condition and return true, the (+) in oracle is telling the sql return true when no data.
When I have data in both Tables but the value in colA (Table1) is null
I am not sure what the where could is doing?
might be :
there is a row in Table1 but colA is NULL so the <= is going to be false
and neither the row in column B or column A should return.
or:
since no row in table 1 or null value in colA equate to the same thing in oracle
then a row is returned because table 2 has a row and column B has a value
Finally
When I have data in both Tables but the value in both columns is null
I am not sure what the where could is doing?
might be :
there is a row in Table1 but colA is NULL so the <= is going to be false
and neither the row in column B or column A should return.
or:
since no row in table 1 or null value in colA equate to the same thing in oracle
a is still not returned because
table 2 has a row and colB is null
the where clause will evaluate colb and since its null the '<=' condition will return false.
I dont have access to a database for a while otherwise i would of justed tested this. But if anyone could confirm or help me
where table1.colA(+) <= table2.colB
I think this means the following:
i am doing an outer join of table1
I have all possible data in table 2, but not all data in table 1
So in the where clause
When I have data in both tables and both columns,
the where will check if colA (in table1) is less than or equal to colB (table 2) (and return true or false based on the data in the columns)
When I have no data in Table1 and data in Table2 and ColB
the where will ignore the condition and return true, the (+) in oracle is telling the sql return true when no data.
When I have data in both Tables but the value in colA (Table1) is null
I am not sure what the where could is doing?
might be :
there is a row in Table1 but colA is NULL so the <= is going to be false
and neither the row in column B or column A should return.
or:
since no row in table 1 or null value in colA equate to the same thing in oracle
then a row is returned because table 2 has a row and column B has a value
Finally
When I have data in both Tables but the value in both columns is null
I am not sure what the where could is doing?
might be :
there is a row in Table1 but colA is NULL so the <= is going to be false
and neither the row in column B or column A should return.
or:
since no row in table 1 or null value in colA equate to the same thing in oracle
a is still not returned because
table 2 has a row and colB is null
the where clause will evaluate colb and since its null the '<=' condition will return false.
I dont have access to a database for a while otherwise i would of justed tested this. But if anyone could confirm or help me
Thats not correct. If you need <= then you can use it in an outer or inner join condition.
operators are operators
no matter where you use them.
operators are operators
no matter where you use them.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is an example :
Table gv$session has session_id (SID ) from instances (INST_ID), 1 and 2
Table gv$px_session has session_id (SID) of sessions with parallel threads running on instances (INST_ID), 1 and 2
SELECT G.INST_ID ID, G.SID SESION,p.inst_id,p.sid
FROM gv$session G,gv$px_session p
WHERE p.inst_id(+) = G.INST_ID
and G.sid(+)=G.sid
order by 3
ID SESION INST_ID SID
---------- ---------- ---------- ----------
1 2711 1 2711
2 1923 2 1923
2 2303 2 2303
1 1935
1 2695
2 1155
1 1555
1 28
2 28
Now , (+) <= , means all the data from right table i.e. G (gv$session) in above case and
matching data (to where clause) from left table i.e. P (gv$px_session)
SELECT G.INST_ID ID, G.SID SESION,p.inst_id,p.sid
FROM gv$session G,gv$px_session p
WHERE G.inst_id(+) = p.INST_ID
and g.sid(+)=p.sid
order by 3
1 2711 1 2711
2 1902 2 1902
2 2303 2 2303
Table gv$session has session_id (SID ) from instances (INST_ID), 1 and 2
Table gv$px_session has session_id (SID) of sessions with parallel threads running on instances (INST_ID), 1 and 2
SELECT G.INST_ID ID, G.SID SESION,p.inst_id,p.sid
FROM gv$session G,gv$px_session p
WHERE p.inst_id(+) = G.INST_ID
and G.sid(+)=G.sid
order by 3
ID SESION INST_ID SID
---------- ---------- ---------- ----------
1 2711 1 2711
2 1923 2 1923
2 2303 2 2303
1 1935
1 2695
2 1155
1 1555
1 28
2 28
Now , (+) <= , means all the data from right table i.e. G (gv$session) in above case and
matching data (to where clause) from left table i.e. P (gv$px_session)
SELECT G.INST_ID ID, G.SID SESION,p.inst_id,p.sid
FROM gv$session G,gv$px_session p
WHERE G.inst_id(+) = p.INST_ID
and g.sid(+)=p.sid
order by 3
1 2711 1 2711
2 1902 2 1902
2 2303 2 2303
with tableB as (
select 1 as id, 'Bnm1' as name from dual union all
select 2 as id, 'Bnm1' as name from dual union all
select 3 as id, 'Bnm1' as name from dual
)
, tableA as (
select 2 as id, 'Anm1' as name from dual union all
select 3 as id, 'Anm1' as name from dual
)
select
'(+) way' method, tableb.id b_id, tablea.id a_id, tableb.name b_nm, tablea.name a_nm
from tableB, tableA
where tableA.id(+) <= tableB.id
UNION ALL
select
'ansi way', tableb.id, tablea.id, tableb.name, tablea.name
from tableB
left join tableA on tableA.id <= tableB.id
order by 1,2,3
result, note that for some rows of tableB there is no match in tableA but using old fashioned (+) or the ANSI standard LEFT JOIN those unmatched rows in tableB are still included in the result
| METHOD | B_ID | A_ID | B_NM | A_NM |
|----------|------|--------|------|--------|
| (+) way | 1 | (null) | Bnm1 | (null) |
| (+) way | 2 | 2 | Bnm1 | Anm1 |
| (+) way | 3 | 2 | Bnm1 | Anm1 |
| (+) way | 3 | 3 | Bnm1 | Anm1 |
| ansi way | 1 | (null) | Bnm1 | (null) |
| ansi way | 2 | 2 | Bnm1 | Anm1 |
| ansi way | 3 | 2 | Bnm1 | Anm1 |
| ansi way | 3 | 3 | Bnm1 | Anm1 |
see http://sqlfiddle.com/#!4/9eecb7/7648
Jhacharya,
Did you try with the suggestions given in above, to your original question? Please update us. Thanks.
Did you try with the suggestions given in above, to your original question? Please update us. Thanks.
For outer join, you should not use '<=' but only
Open in new window