create database opt;
go
use opt;
go
create table t1(a int primary key, b int not null, c int check (c between 1 and 50));
create table t2(b int primary key, c int, d char(10));
create table t3(c int primary key);
go
insert into t1(a,b,c) select number, number%100+1, number%50+1 from master..spt_values where type = 'p' and number between 1 and 1000;
insert into t2(b,c) select number, number%100+1 from master..spt_values where type = 'p' and number between 1 and 1000;
insert into t3(c) select number from master..spt_values where type = 'p' and number between 1 and 1000;
go
alter table t1 add constraint fk_t2_b foreign key (b) references t2(b);

/* 1. */
use opt;
go
--Error
select *
from
	t1
	join t2 on t1.a = t2.b
where
	t1.a = 1
option(recompile,merge join,hash join);
go
--Ok
select *
from
	t1
	join t2 on t1.a+1 = t2.b
where
	t1.a = 1
option(recompile,merge join,hash join);
go

/* 2. */
--Error
select *
from
	t1
	join t2 on t1.a = t2.b
where
	t1.a in (1)
option(recompile,merge join,hash join);
go
--Ok
select *
from
	t1
	join t2 on t1.a+1 = t2.b
where
	t1.a in (1,2)
option(recompile,merge join,hash join);
go

/* 3. */
--Error
select *
from
	t1
	join t2 on t1.a = t2.b and t1.b = t2.c
where
	t1.a = 1 and
	t1.b = 1
option(recompile,merge join,hash join);
go
--Ok
select *
from
	t1
	join t2 on t1.a = t2.b and t1.b = t2.c
where
	t1.a = 1
option(recompile,merge join,hash join);
go

/* 4. */
--Error
declare @a int = null;
select 
	*
from 
	t1 
	join t2 on t1.a = t2.b
where
	t1.a = @a or @a is null	
option(recompile, merge join);
go
--Ok
declare @a int = 1;
select 
	*
from 
	t1 
	join t2 on t1.a = t2.b
where
	t1.a = @a or @a is null	
option(recompile, merge join);

/* 2. */
--Error
select *
from
	t1
	join t2 on t1.a = t2.b
where
	t1.a in (1)
option(recompile,merge join,hash join);
go
--Ok
select *
from
	t1
	join t2 on t1.a = t2.b
where
	t1.a in (1,2)
option(recompile,merge join,hash join);
go