changing from old style to new style joins

Question

I have been trying to change my old style of SQL from cross to joins, but I can't manage to make it work.

Here is my code:

SELECT 'EFE' tipo,
    c.empnum,
    c.succlave,
    d.tipopago,
    d.tjcredito
FROM detcobros d,
    cobros c,
    masterbancos b,
    monedas m
WHERE     d.empnum = c.empnum
    AND d.succlave = c.succlave
    AND d.cobfolio = c.cobfolio
    AND d.PaisClave = b.PaisClave(+)
    AND d.bannum = b.bannum(+)
    AND d.monNum = m.monNum
    AND d.tipopago = 'EF'
    AND (   (c.status IN ('AU', 'US', 'UP'))
         OR (    c.status = 'CA'
             AND NVL (c.cortecaja, 0) <> NVL (c.cortecajacanc, 0)))
    AND c.empnum = 255
    AND c.succlave = 'CDCU'
    AND c.cortecaja = 3004

And here what I've tried:

SELECT 'EFE' tipo,
    c.empnum,
    c.succlave,
    d.tipopago,
    d.tjcredito
FROM detcobros d
    JOIN cobros c ON c.empnum   = d.empnum
    and c.succlave = d.succlave
    and c.cobfolio = d.cobfolio
    RIGHT JOIN masterbancos b ON b.PaisClave = d.PaisClave
    and  b.bannum    = d.bannum
    JOIN monedas m ON m.monNum  = d.monNum
WHERE d.tipopago = 'EF'
    AND (   (c.status IN ('AU', 'US', 'UP'))
         OR (    c.status = 'CA'
             AND NVL (c.cortecaja, 0) <> NVL (c.cortecajacanc, 0)))
    AND c.empnum = 255
    AND c.succlave = 'CDCU'
    AND c.cortecaja = 3004

The first query returns data but the second doesn't get any data.

To troubleshoot where you're breaking down, just do a `SELECT *` then remove your `JOIN`s and `WHERE`s until you find the one that's breaking your results. I would also say that you should move you `c.` conditions up into the `JOIN` on `cobros c`, but I don't know what your data looks like. Think of the data in _sets_. What do you want to return from each table? — Shawn, Jan 25 '18 at 16:53
I'm not great with the old syntax, but I believe you wanted `LEFT JOIN` not `RIGHT JOIN`. — Chris Hep, Jan 25 '18 at 16:58
`JOIN monedas m ON m.monNum = d.monNum` may be your problem. — Shawn, Jan 25 '18 at 17:06
do i have to do a double left join or with one is correct LEFT JOIN masterbancos b ON b.PaisClave = d.PaisClave LEFT JOIN masterbancos b.bannum = d.bannum — tseres, Jan 25 '18 at 17:19
Ignore my previous comment. I misread the original query. The `JOIN` on `monedas` is correct. It should be a `LEFT OUTER JOIN` on `masterbancos` instead of a `RIGHT OUTER JOIN`, since the original criteria had the `(+)` on the `b.` columns. — Shawn, Jan 25 '18 at 17:29

score 3 · Accepted Answer · edited Jan 26 '18 at 10:17

3

Try it with LEFT JOIN because the + operator is on the right.

syntax + operator

edited Jan 26 '18 at 10:17

William Robertson

15,273
4
38
44

answered Jan 25 '18 at 16:58

André Müller Pereira

68
6

1

Perhaps this could be made clearer, as `d.PaisClave = b.PaisClave(+)` is the same thing as `b.PaisClave(+) = d.PaisClave` (the latter is more logical, but for some reason writing joins backwards is a popular anti-pattern). – William Robertson Jan 26 '18 at 10:26

score 1 · Answer 2 · answered Jan 25 '18 at 17:22

As I mentioned above, think of your data in the sets of data that you want to get from each table.

Essentially, a SQL engine will process your query in the following basic order:

FROM (incl JOINs) -> CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

For your query, I would recommend moving all of your relevant conditions up into your JOINs to minimize the sets of data that will be JOINed together.

I would recommend:

SELECT 'EFE' AS tipo
    , c.empnum
    , c.succlave
    , d.tipopago
    , d.tjcredito
FROM detcobros d
INNER JOIN cobros c ON d.empnum = c.empnum
    AND d.succlave = c.succlave
    AND d.cobfolio = c.cobfolio
    AND c.empnum = 255
    AND c.succlave = 'CDCU'
    AND c.cortecaja = 3004
    AND (   
          c.status IN ('AU', 'US', 'UP')
          OR (
              c.status = 'CA'
              AND NVL (c.cortecaja, 0) <> NVL (c.cortecajacanc, 0)
          )
    )
LEFT OUTER JOIN masterbancos b ON d.PaisClave = b.PaisClave
    AND d.bannum = b.bannum
INNER JOIN monedas m ON d.monNum = m.monNum
WHERE d.tipopago = 'EF'

This does include some of my own personal preferences (like using AS in SELECT column aliases but not in table aliases. Also, I believe your query broke on JOIN monedas m ON m.monNum = d.monNum. You specified two different types of JOINs between the queries.

I would add that moving the `JOIN` conditions from the `WHERE` to the `JOIN` probably won't have an effect for `INNER JOIN`s but may change the results on `OUTER JOIN`s. See my above list of order of execution to work towards what exactly data it is that you need returned. — Shawn, Jan 25 '18 at 17:32

William Robertson · Answer 3 · 2018-01-26T00:20:17.680

Here's what I got:

select 'EFE' tipo
     , c.empnum
     , c.succlave
     , d.tipopago
     , d.tjcredito
from   cobros c
       join detcobros d
            on   d.empnum = c.empnum
            and  d.succlave = c.succlave
            and  d.cobfolio = c.cobfolio
       join monedas m
            and  m.monnum = d.monnum
       left join masterbancos b
            and  b.paisclave = d.paisclave
            and  b.bannum = d.bannum
where  c.empnum = 255
and    c.succlave = 'CDCU'
and    c.cortecaja = 3004
and    d.tipopago = 'EF'
and    (   (c.status in ('AU', 'US', 'UP'))
        or (c.status = 'CA' and nvl(c.cortecaja, 0) <> nvl(c.cortecajacanc, 0)) );

My personal preference is to place the outer joins at the end of the from clause. Also the keywords outer and inner are redundant clutter so I never use them.

cobros seemed to be the logical starting place as it has the most predicates in the where clause. (The optimiser doesn't care, of course.)

A right join is just a left join written backwards. I never use them, I just arrange the tables the other (normal) way around.

The filtering conditions remain in the where clause (unless they apply to an outer join in which case they need to be included in the join - but there weren't any like that here). I'm sure this can be argued either way, but I like to see joins and filtering conditions listed separately.

I've also fixed your old-style uppercase for you ;)

`outer` is not clutter when used with a `full outer join` which is not relevant to this particular question but to say one "never" uses `outer` is not a certainty. — Michael O'Neill, Jan 25 '18 at 20:13
Now that I check, the `outer` keyword is optional in `full join` as well. https://docs.oracle.com/database/121/SQLRF/statements_10002.htm — William Robertson, Jan 26 '18 at 00:18
Thanks @William - learning something new every day is a good thing. — Michael O'Neill, Jan 29 '18 at 17:13

changing from old style to new style joins

3 Answers3