sql - Getting extra rows - After joing the 3 tables using Left Join

Question

SELECT (b.descr || ' - ' || c.descr) description
  FROM tbl1 a LEFT JOIN tbl2 b ON a.ACCOUNT = b.ACCOUNT 
       LEFT JOIN tbl3 c ON a.product = c.product
 WHERE a.descr50 = ' ' ;  

table1 has only 7622 rows with descr50 = ' ' but this select is returning 7649 rows. Could you please help me in this? thanks in advance

Answer 1

When you JOIN two or more table together, you effectively get a cartesian product for these tables to which a filter stated in the JOIN condition is applied.

This is more obvious when you use an obsolete implicit JOIN syntax.

The LEFT JOIN guarantees that you get no less rows than the leftmost table contains, i. e. each row from the leftmost table is returned at least once.

You can still get more rows, if the filter is not a one-to-one row mapping.

In your case:

SELECT  (b.descr || ' - ' || c.descr) description
FROM    tbl1 a
LEFT JOIN
        tbl2 b
ON      b.ACCOUNT = a.ACCOUNT
LEFT JOIN
        tbl3 c
ON      c.product = a.product
WHERE  a.descr50 = ' '

either acccount or product are not unique in b or c.

For these rows:

a.account

1
2
3

b.account  b.description

1          Account 1
2          Account 2 - old
2          Account 2 - new

, the JOIN will return the following:

a.account b.account b.description

1         1          Account 1
2         2          Account 2 - old
2         2          Account 2 - new
3         NULL       NULL

, giving you more rows than either of the tables contains.

To just pick the first matching description from either table, use this:

SELECT  (
        SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
        FROM    tbl2 b
        WHERE   b.account = a.account
                AND rownum = 1
        ) || ' - ' ||
        (
        SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
        FROM    tbl3 c
        WHERE   c.product= a.product
                AND rownum = 1
        ) description
FROM    tbl1 a
WHERE   a.descr50 = ' '

To update, just wrap the query into an inline view:

UPDATE  (
        SELECT  (
                SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
                FROM    tbl2 b
                WHERE   b.account = a.account
                        AND rownum = 1
                ) || ' - ' ||
                (
                SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
                FROM    tbl3 c
                WHERE   c.product= a.product
                        AND rownum = 1
                ) description
        FROM    tbl1 a
        WHERE   a.descr50 = ' '
        )
SET     descr50 = description