2

Why is there a difference in output for the below queries?

As per my understanding it should give the same output for the first 3 queries and same output for the last 3 queries.

select count(*) 
from CTM_People a left outer join user_x b 
     on a.remedy_login_id = b.login_name;

Result 1: 152771

select count(*) 
from CTM_People a left outer join user_x b 
     on a.remedy_login_id = b.login_name 
where a.remedy_login_id like '%';

Result 2: 151185

select count(*) 
from CTM_People a left outer join user_x b 
     on a.remedy_login_id = b.login_name and a.remedy_login_id like '%';

Result 3: 152771

select count(*) 
from CTM_People a right outer join user_x b 
     on a.remedy_login_id = b.login_name;

Result 1: 150899

select count(*) 
from CTM_People a right outer join user_x b 
     on a.remedy_login_id = b.login_name where a.remedy_login_id like '%';

Result 2: 150889

select count(*) 
from CTM_People a right outer join user_x b 
     on a.remedy_login_id = b.login_name and a.remedy_login_id like '%';

Result 3: 150899

Total count in both the tables are

select count(*) from CTM_People; **Result : 152771**
select count(*) from user_x; **Result : 150899**
Tanner
  • 22,205
  • 9
  • 65
  • 83
Subbu
  • 41
  • 1
  • 8
  • 3
    LIKE operator doesn't accept NULLs. Do you have any NULL values in a.remedy_login_id? – David P Feb 25 '15 at 16:01
  • If you're using Oracle, please adjust your tags and remove MySQL and SQL Server. – Phoenix Feb 25 '15 at 16:04
  • There's probably null values like @DavidP mentioned, 3rd isn't affected because the '%' is the outer join criteria and the other difference depends on which table you outer join. – James Z Feb 25 '15 at 16:05
  • Thanks David and James. I got it. CTM_People table actually had 1586 null values. – Subbu Feb 25 '15 at 17:47
  • I added my previous comment as an answer so you can accept or whatever. – David P Feb 26 '15 at 13:25

1 Answers1

0

LIKE operator doesn't accept NULLs. Please check a.remedy_login_id

David P
  • 2,027
  • 3
  • 15
  • 27