elect 1.id,t1.name,t2.monicker
from t1 left join t2
on t1.id=t2.id and charindex(t1.name,t2.monicker)<>0
我们运行下这个查询,然后我们可以自己体会到“原样保留”这4个字的意思是不删除,也不放大查询中指定不按照Join条件进行过滤筛选的结果。
在join 的时候其实可以充分发挥你的想象力,我们不仅可以使用任何可能使用的可以出现逻辑结果的运算符号,还可以使用任何确定/不确定/运算出来的列值进行join
看看这个例子(从老文章里抄的):
emp_no name
001 Tom
002 Green
003 Sam
004 Sun
005 Hale
想变成两列显示
emp_no1 name1 emp_no2 name2
001 Tom 004 Sun
002 Green 005 Hale
003 Sam
于是有了
select e1.name as name1,e1.emp_no as emp_no1,e2.name as name2,e2.emp_no as emp_no2
from
(select emp.* ,(select count(*) from emp x where x.emp_no<=emp.emp_no) as cnt from emp) e1,
(select emp.* ,(select count(*) from emp x where x.emp_no<=emp.emp_no) as cnt from emp) e2,
where (e1.cnt-((select count(*) from emp)/2))*=e2.cnt
现在,你对join 的可以发挥想象力的能力还有多少怀疑?
由于多表连接可以有层次理解,最终也可以简化成2表连接的关系,就不多说了。
join 在数学的理解上可以认为是对两个表的笛卡儿积进行筛选,换句话说,join 能得到的最大集是笛卡儿积,最小集是0行的结果集。
为了讨论,我们先假设有表
t1:
id name
1 Tom
2 Sum
3 Lee
t2:
id monicker
1 Fat Tom
1 Picker
2 Heller
查询
select 1.id,t1.name,t2.monicker from t1, t2 ( select 1.id,t1.name,t2.monicker from t1 cross join t2)
就是返回两个表的笛卡儿积,将返回一个9行的数据,这个9行数据的成因是什么呢?自然是因为对于t1的每行数据在t2 里都有3种跟他join 的可能性。
但是我们承认这样的查询除了获得体育彩票之外,在绝大部分情况下都不是我们需要的查询。
最常用的筛选符号自然是“=”号把上面的语句改成:
select 1.id,t1.name,t2.monicker from t1 join t2
on t1.id=t2.id
(select t1.id,t1.name,t2.monicker from t1, t2
where t1.id=t2.id)
就是我们最常用的join 类型了。
但请注意,我们说过我们“进行筛选”,但是没有说过用什么符号进行筛选,看看下面这个查询
select 1.id,t1.name,t2.monicker from t1 join t2
on 1=1
结果就相当于cross join 了
那请思考一下下面这个 查询有什么效果
select 1.id,t1.name,t2.monicker
from t1 join t2
on t1.id=t2.id and charindex(t1.name,t2.monicker)<>0