Geek Answers Handbook

The query does not return any rows with the same "%" operator and left outer join if there are no rows in the right table

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 '%%';

The above written query does not return any records, although there are 5 records in the left table. without a filter on the right table it works fine.

+5
source share
5 answers
 select A.* from A left outer join B on A.id = B.id

this request will give you the opportunity to do so ...

 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 compare username using for example null

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

therefore, he will not give you any way out

you should try the 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)

+4
source

In your scenario ... the first left join occurs, it dials 5 records, and then sql sever applies the user_name filter on this recordset and as user_name it has zero value for all rows. No entries are displayed.

you can change your request to

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

You use wild card to compare null values, use this,

 SELECT a.* FROM a LEFT OUTER JOIN b ON a.id = b.id WHERE b.user_name LIKE '%' OR b.user_name IS NULL;
+3
source

Since all values ​​in table B are NULL , any wildcard match for NULL values ​​returns NULL .

Thus, the condition where B.user_name like '%'; converted to where NULL like '%'; which evaluates to NULL, because NULL cannot be compared to any value.

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

sql script support: http://sqlfiddle.com/#!6/1ca91/8

Note that COALESCE is ANSI and therefore supported by Oracle, SQL Server, and PostGres and provides an overview. P

Edit: Based on new information, this same query should work in all SQL Server, PostGres, and Oracle. I am changing the SQL query to use COALESCE instead, which is supported in all

If you are not using ISNULL() and check this as where ISNULL(B.user_name,'') like '%';

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

See this fiddle http://sqlfiddle.com/#!6/1ca91/6

+2
source

Try the following:

 select A.* from A left outer join (SELECT * FROM B where user_name like '%') X on A.id = X.id;
0
source

More articles:


All Articles