Query is not returning any rows with like '%' operator and left outer join, if right table has no rows

Question

I am using MS SQL SERVER 2008R2. i have two tables A and B as

create table A(
id int primary key, name Varchar(20));

create table B(
id int primary key, user_name Varchar(20));

insert into A values(1,'A1');
insert into A values(2,'A2');
insert into A values(3,'A3');
insert into A values(4,'A4');
insert into A values(5,'A5');

Now my problem is :

select A.* 
from A left outer join B on A.id = B.id 
where B.user_name like '%';

or

select A.* 
from A left outer join B on A.id = B.id 
where B.user_name like '%%';

Above written query does not return any records even though left table have 5 entries in it. without any filter on right table it works fine.

Answer 1

select A.* from A left outer join B on A.id = B.id 

this query will give you out put like this...

id  name    id  user_name
1   A1  NULL    NULL
2   A2  NULL    NULL
3   A3  NULL    NULL
4   A4  NULL    NULL
5   A5  NULL    NULL

and you are comparing username using like with null

select A.* from A left outer join B on A.id = B.id where B.user_name like '%%';

hence it will not give you any output

you should try following query

select A.*,b.* from A left outer join B on A.id = B.id where (b.user_name like '%%' or b.user_name is null)

Answer 2

In your scenario...first left join is happening it is finding 5 entries and then on that record set sql sever is applying filter of user_name and as user_name for all rows is null..no records are getting displayed.

you can change your query to

select A.* from A left outer join B on A.id = B.id where ISNULL(B.user_name,'') like '%%';