select *
from proj.busrepts busr, proj.pbase pb, proj.WQuota wq, finance.pedproj pedp, finance.qiiview qii
where busr.projectid = pedp.projectid(+) and
busr.program = pedp.pedprogram and
busr.patnum = pb.fileno(+) and
busr.curtype like '%Self%' and
busr.wq_statusid = wq.statusid(+) and
busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY') and
busr.balance >5 and
(qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%'
and qii.address not like '%HOME%' and qii.address not like 'Unk%'
and qii.address not like 'UNK%')
and busr.projectid = qii.projectid(+) and
busr.program = qii.program
and wq.name in ('Project Invoice Cycle Alpha')
and busr.program in (51,91,161,211,231,271,351)
and busr.wq_followup = TRUNC(sysdate)
from proj.busrepts busr
, proj.pbase pb ON
, proj.WQuota wq ON
, finance.pedproj pedp ON
, finance.qiiview qii ON
from proj.busrepts busr
, proj.pbase pb ON
, proj.WQuota wq ON
, finance.pedproj pedp ON busr.projectid = pedp.projectid(+) --<< PASTE here
, finance.qiiview qii ON
where and --CUT was here
from proj.busrepts busr
, proj.pbase pb ON busr.patnum = pb.fileno(+)
, proj.WQuota wq ON busr.wq_statusid = wq.statusid(+)
, finance.pedproj pedp ON busr.projectid = pedp.projectid(+) AND busr.program = pedp.pedprogram
, finance.qiiview qii ON busr.projectid = qii.projectid(+) AND busr.program = qii.program
where and
and
and
busr.curtype like '%Self%' and
and
busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY') and
busr.balance >5 and
(qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%'
and qii.address not like '%HOME%' and qii.address not like 'Unk%'
and qii.address not like 'UNK%')
and and
and wq.name in ('Project Invoice Cycle Alpha')
and busr.program in (51,91,161,211,231,271,351)
and busr.wq_followup = TRUNC(sysdate)
from proj.busrepts busr
LEFT JOIN proj.pbase pb ON busr.patnum = pb.fileno(+)
LEFT JOIN proj.WQuota wq ON busr.wq_statusid = wq.statusid(+) and wq.name in ('Project Invoice Cycle Alpha')
LEFT JOIN finance.pedproj pedp ON busr.projectid = pedp.projectid(+) AND busr.program = pedp.pedprogram
LEFT JOIN finance.qiiview qii ON busr.projectid = qii.projectid(+) AND busr.program = qii.program and
(qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%'
and qii.address not like '%HOME%' and qii.address not like 'Unk%'
and qii.address not like 'UNK%')
where and
and
and
busr.curtype like '%Self%' and
and
busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY') and
busr.balance >5
and and
and busr.program in (51,91,161,211,231,271,351)
and busr.wq_followup = TRUNC(sysdate)
from proj.busrepts busr
LEFT JOIN proj.pbase pb ON busr.patnum = pb.fileno
LEFT JOIN proj.WQuota wq ON busr.wq_statusid = wq.statusid and wq.name in ('Project Invoice Cycle Alpha')
LEFT JOIN finance.pedproj pedp ON busr.projectid = pedp.projectid AND busr.program = pedp.pedprogram
LEFT JOIN finance.qiiview qii ON busr.projectid = qii.projectid AND busr.program = qii.program and
(qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%'
and qii.address not like '%HOME%' and qii.address not like 'Unk%'
and qii.address not like 'UNK%')
where busr.curtype like '%Self%' and
and busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY')
and busr.balance >5
and busr.program in (51,91,161,211,231,271,351)
and busr.wq_followup = TRUNC(sysdate)
FROM proj.busrepts busr
LEFT JOIN proj.pbase pb ON busr.patnum = pb.fileno
LEFT JOIN proj.WQuota wq ON busr.wq_statusid = wq.statusid
AND wq.NAME IN ('Project Invoice Cycle Alpha')
LEFT JOIN finance.pedproj pedp ON busr.projectid = pedp.projectid
AND busr.program = pedp.pedprogram
LEFT JOIN finance.qiiview qii ON busr.projectid = qii.projectid
AND busr.program = qii.program
AND (
qii.address NOT IN ('1 Pudding Lane', 'Diagon Alley')
AND qii.address NOT LIKE '%Home%'
AND qii.address NOT LIKE '%HOME%'
AND qii.address NOT LIKE 'Unk%'
AND qii.address NOT LIKE 'UNK%'
)
WHERE busr.curtype LIKE '%Self%'
AND busr.DOS >= TO_DATE('01-JUL-2010', 'DD-MON-YYYY')
AND busr.balance > 5
AND busr.program IN (51, 91, 161, 211, 231, 271, 351)
AND busr.wq_followup = TRUNC(sysdate)
The above example was derived from a question here at EE and it happens that all the joins in that example are LEFT OUTER JOINS. Let's now look at a series of examples for other join types.
from table1, table2 where table1.id = table2.fk_id;
-- step 1
from table1
, table2 ON
where table1.id = table2.fk_id
-- step 3
from table1
, table2 ON table1.id = table2.fk_id
where
-- step 7
from table1
INNER JOIN table2 ON table1.id = table2.fk_id
from table1, table2 where table1.nm = 'butterfly'
-- step 1
from table1
, table2 ON
where table1.nm = 'butterfly'
-- step 3
from table1
, table2 ON
where table1.nm = 'butterfly'
-- HOLD ON, there is nothing like: table.field = table.field
-- nothing that refers to table2 at all in the where clause, could it just be a mistake? (needs testing!)
-- step 7
from table1
CROSS JOIN table2
where table1.nm = 'butterfly'
So, if there is nothing in the where clause that references a table and consequently there is nothing after ON, this is either a deliberate CROSS JOIN - or - it is a mistake. Believe me when I say this mistake is more common than you may think, so be on guard for tables left with "nothing" to join by.
from table1, table2 where table1.fk_id(+) = table2.id and table2.code = 'x'
-- step 1
from table1
, table2 ON
where
and table2.code = 'x'
-- step 3
from table1
, table2 ON table1.fk_id(+) = table2.id
where
and table2.code = 'x'
-- HOLD ON the secret code applies to the "FROM table"
-- so, NEED something different, could be
-- A: a right outer join
-- step 7A
from table1
RIGHT JOIN table2 ON table1.fk_id = table2.id
where table2.code = 'x'
-- B: flip the order of tables
-- step 7B
from table2
LEFT JOIN table1 ON table2.id = table1.fk_id
where table2.code = 'x'
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
Mike