Skip to Content
0

Different number of lines after LEFT OUTER JOIN with LIKE condition

Jul 30, 2017 at 01:39 PM

47

avatar image

Hello everybody! We store the mappings as a separate table. The data is planned to be mapped by connecting tables with data and tables with a mapping using LEFT UTER JOIN LIKE.

I encountered the following problem: the number of rows in the source table differs from the number of lines of the target after applying an LEFT OUTER JOIN by the LIKE condition.

In one case, the number of rows becomes smaller, and in the other, the number increases more. Can you tell me what could be the reasons for this problem?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Ravi kiran Pagidi Jul 31, 2017 at 08:44 AM
0

Hi Станислав Уколкин,

The number of rows in target is decreased because LIKE is filtering out the records.

In other case it is increasing might be because the column values from left table (columns on which you are joining) are present multiple times in the right table.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Ravi, I think this could be true if INNER JOIN was used. But according to set theory, LEFT OUTER JOIN should return as many rows as there are in the left table. That is, if the row from the source table does not have a suitable rule in the mapping, the value NULL must be set.

Or am I not understanding something?

0

Hi Станислав Уколкин,

Yes true, LEFT OUTER JOIN would return as many rows as there are in the left table. But since you have LIKE condition along with JOIN condition, it must be filtering out the records. Because the records that failed the LIKE condition are not going to the target.

0