【排列组合场景】 =、!=、＜＞、＜=＞使用

一个叫team的表，里面只有一个字段name,一共有4条记录，分别是a,b,c,d对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合

create table team(
 name char(1)
);

insert into team values('a');
insert into team values('b');
insert into team values('c');
insert into team values('d');

select a.name as A,b.name as B from team a,team b order by a.name ; -- 没有去重 aa  bb 类型


select a.name as A,b.name as B from team a,team b where a.name != b.name order by a.name ; -- 没有去重 ab ba 类型  

select a.name as A,b.name as B from team a,team b where a.name <> b.name order by a.name ; -- 没有去重 != 等同于<>的效果

# 利用不等关系去重

select a.name as A,b.name as B from team a,team b where a.name > b.name order by a.name ; -- 去重成功
select a.name as A,b.name as B from team a,team b where a.name < b.name order by a.name ; -- 去重成功



-- != 与 <>的区别

insert into team values (null)

select * from team where name != null; -- 判空无效    
select * from team where name <> null; -- 判空无效
select * from team where name is not null; -- 判空生效


select * from team where name != 'a'; -- 判断左右是否相等，不包含null值
select * from team where name <> 'a'; -- 判断左右是否相等，不包含null值


-- 结论：
--  =  !=  不用来判空，用is null   is not null
-- !=和<>都是不等于的意思，只能说标准不一样，!=在高级语言中表示不等于，<>在ANSI标准中表示不等于
-- 在各种数据库中，基本都支持!= 和 <> ，但也有只支持ANSI标准的数据库软件，所以为了通用还是建议采用<>

-- 如果想筛选出4条记录：b c d null,可以这么写
select * from team where name <> 'a' or name is null;


-- <> 和 <=>的区别
-- <> 是判断两边的字段值是否不等，不等返回true，不包含null
-- <=>是判断两边的字段值是否相等，相等返回true，包含null

select a.name as A,b.name as B from team a,team b where a.name <> b.name order by a.name ;

select a.name as A,b.name as B from team a,team b where a.name <=> b.name order by a.name ;