INNER JOIN with OR + WHERE

1

Hey everyone, when I do a INNER JOIN with OR and WHERE my table alters my numeric values in a not expected way. Example:

SELECT columns FROM t1
INNER JOIN t2
ON t1.id1=t2.id1
OR t1.id1=t2.id2
OR t1.id1=t2.id3

This is ok, but when I end with an
WHERE status = ‘concluded’

The values change a lot: they where <50k, and get bigger than this value with the where.
Can someone help me?

2

@naiaracerqueira
Could you do a RUN (not a Preview) of the following queries:

SELECT COUNT(*) FROM (
SELECT columns FROM t1
INNER JOIN t2
ON t1.id1=t2.id1
OR t1.id1=t2.id2
OR t1.id1=t2.id3
)

And also this:

SELECT COUNT(*) FROM (
SELECT columns FROM t1
INNER JOIN t2
ON t1.id1=t2.id1
OR t1.id1=t2.id2
OR t1.id1=t2.id3
WHERE status = ‘concluded’
)
3

Hey, thanks for replying! We tried COUNT() and SUM(), just to double check, and we got this:

  • Without WHERE: Count(*) = 27707, Sum(result) = 7.471051641E9
  • With WHERE: Count(*)= 14328, Sum(result) = 1.289985664601E13
4

Hi @naiaracerqueira - this looks right (the count of filtered rows is less than the count without the filter). Could you please explain what the problem is again?

5

Hi @lenoy, the problem is my numeric value like the “result” column, so lets say I created a 3x5 table with the following code

SELECT columns FROM t1
INNER JOIN t2
ON t1.id1=t2.id1
OR t1.id1=t2.id2
OR t1.id1=t2.id3

I get something like this:
| id | status | result |
| 1 | concluded | 100 |
| 2 | pending | 50 |
| 3 | concluded | 150 |
| 4 | pending | 80 |

But when I the filter Where = ‘concluded’ my numeric value gets super high:
| id | status | result |
| 1 | concluded | 50000E7 |
| 3 | concluded | 300000E7 |

The real sum of this values is like:

  • Without WHERE: Sum(result) = 7.471051641E9
  • With WHERE: Sum(result) = 1.289985664601E13
6

Many ways to skin the cat, but I always habitually did this kind of stuff because of unpredictable results in other SQL engines as

SELECT columns FROM
(
SELECT columns FROM t1
INNER JOIN t2
ON t1.id1=t2.id1
UNION
SELECT columns FROM t1
INNER JOIN t2
ON t1.id1=t2.id2
UNION
SELECT columns FROM t1
INNER JOIN t2
ON t1.id1=t2.id3
) z
WHERE status = ‘concluded’

One of the other reasons to do this way in more conventional engines was also performance. Sometimes you’d avoid a table scan.

It’s also more clear in many ways, but YMMV

1 Like
7

@spireite Thanks a lot! It worked!