如果右表没有行，则查询不返回任何具有类似'％'运算符和左外连接的行

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

我正在使用MS SQL SERVER 2008R2。我有两张桌子A和B.

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

要么

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.

上面写入的查询不会返回任何记录，即使左表中有5个条目。在右表上没有任何过滤器它工作正常。

5 个解决方案

#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

并且您正在使用like与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)

从A.id = B.id上的左外连接B中选择A。*，b。*其中（b.user_name像'%%'或b.user_name为null）

#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.

在您的方案中...第一个左连接发生它正在查找5个条目，然后在该记录集上sql sever正在应用user_name的过滤器，并且所有行的user_name都为null ..没有显示记录。

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

#3

You are using wild card for comparing null values as well, 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;

#4

Since all values in table B are NULL, any wildcard match on NULL values will return NULL.

由于表B中的所有值都为NULL，因此NULL值上的任何通配符匹配都将返回NULL。

So the condition where B.user_name like '%'; translates into where NULL like '%'; which evaluates to NULL as NULL cannot be compared with any value.

所以B.user_name喜欢'％'的条件;转换为NULL，如'％';计算为NULL的NULL不能与任何值进行比较。

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

supporting sql fiddle : http://sqlfiddle.com/#!6/1ca91/8

支持sql小提琴：http：//sqlfiddle.com/#！6/1ca91 / 8

Note that the COALESCE is ANSI, and therefore supported in Oracle, SQL Server and PostGres and does shortcut evaluation. n

请注意，COALESCE是ANSI，因此在Oracle，SQL Server和PostGres中受支持，并进行快捷方式评估。 ñ

Edit: Based on new information that 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

编辑：基于新信息，这个查询应该适用于所有SQL Server，PostGres和Oracle。我正在更改SQL查询以使用COALESCE，而所有这些都支持

Unless you use ISNULL() and check like this where ISNULL(B.user_name,'') like '%';

除非你使用ISNULL（）并像这样检查ISNULL（B.user_name，''），如'％';
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

看到这个小提琴http://sqlfiddle.com/#!6/1ca91/6

#5

Please try this one:

请试试这个：

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

#1

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