Solved

where  table1.colA(+) <= table2.colB

Posted on 2016-09-21
7
61 Views
Last Modified: 2016-10-12
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
0
Comment
Question by:jhacharya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41810143
Hi,
For outer join, you should not use '<=' but only

where  table1.colA(+) = table2.colB

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41810392
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.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points (awarded by participants)
ID: 41810397
where  table1.colA(+) <= table2.colB

Can be re-written as:

From table2
Left join table1 on  table1.colA <= table2.colB

The old (+) syntax used by Oracle is used to indicate that unmatched rows can still be returned by the query (an outer join)
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 5

Expert Comment

by:Abhimanyu Suri
ID: 41811309
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41811623
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

Open in new window

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 |

Open in new window

see http://sqlfiddle.com/#!4/9eecb7/7648
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41832846
Jhacharya,
Did you try with the suggestions given in above, to your original question? Please update us. Thanks.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question