-1

I finally figured out the answer but it might be useful for someone looking up the same question so here is the whole write up to hopefully save you the frustration.

Problem I had: Toad's query builder has 3 tabs - Diagram, Query and Results. I adjusted my query to what I wanted in Query tab. Ran it and confirmed that Results tab looked good. Then, I wanted to sync Query to Diagram. After syncing I noticed that my LEFT OUTER JOIN was turned into a RIGHT OUTER JOIN. Why? Won't the results be wrong?

I wanted all records from INVOICES table and only matching records from INVOICE_ITEMS table. My original query's FROM statement was:

FROM INVOICES
  LEFT OUTER JOIN INVOICE_ITEMS
     ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)

And Toad kept turning it into:

FROM INVOICES
  RIGHT OUTER JOIN INVOICE_ITEMS
     ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)

It was driving me crazy. Why was it doing that?

Well, the solution was simple. I apparently reversed my tables and Toad was smart enough to fix it for me. (Facepalm) I'm still not sure if it's a good feature or not considering my confusion but it's definitely there.

So, the correct syntax for LEFT OUTER JOIN was supposed to be:

FROM INVOICES
  LEFT OUTER JOIN INVOICE_ITEMS
     ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
  • Do both tables have two columns, INVOICE_GKEY and GKEY? Which one is the PK in each table? – ypercubeᵀᴹ Apr 11 '18 at 19:24
  • @ypercube GKEY is the PK for INVOICES table. INVOICE_GKEY is FK in INVOICE_ITMES table. I don't know why all keys are called GKEYs in this database. – Kamiccola Apr 20 '18 at 19:38
0

The correct syntax for LEFT OUTER JOIN was supposed to be:

FROM INVOICES
  LEFT OUTER JOIN INVOICE_ITEMS
     ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)

the table on the left of my statement is the one I want all records of and the table on the right is the one I want only matching records.

-2

Ok folks, if you're going to do joins, do it RIGHT.

Select from table A, table B where B-fields match the A-fields.

table A is the DRIVER, table B gets selected because of the DRIVER so the DRIVER fields GO FIRST.

if you want all of A (whether or not there are B's) and "any" B's that match, yes, you use an "outer" join.

So in this case ...

select (columns)
  from Invoice_master M
LEFT outer join Invoice_items I
on I.Item_Parent = M.Invoice_Key

... makes a lot of sense.

What doesn't makes sense is, you SAID Invoice_GKey was in the child-item, but GKey was in the parent-master and then you proceed to swap the keys and references, so no wonder Toad changed them.

Me, I would use the old syntax, which (to me) was clearer, assuming Invoice_Master has Master_Key as unique, and Invoice_Items has Parent_Key as indexed-Foreign-key, constrained to that Master_Key, -AND- an "Item_Key" where Parent_Key plus Item_Key is a unique combo ...

select (m.master-inv-columns, i.item-cols)
from Invoice_Master M, Invoice_items I
where I.Parent_Key(+) = M.Master_Key;

I only use outer-join syntax when dealing with multi-part keys and this is just a single-key-field join.

  • Even Oracle recommends to stop using the outdated, proprietary (+) operator – a_horse_with_no_name Mar 1 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.