SQL Server 2008 T-SQL编程系列课程之常用查询算法比较

create table table_A(id int, testCol varchar(256));
create table table_B(id int, testCol varchar(256));
insert into table_A
select 1,'A'
union all
select 2,'B';

insert into table_B
select 1,'A'
union all
select 2,'B';

1.from
2.on
3.join
4.where

select * from table_A,table_B
where table_A.id=table_B.id;

建议写法
select * from table_A inner join table_B on table_A.id=table_B.id;

select * from table_A left join table_B on table_A.id=table_B.id;

select * from table_A right join table_B on table_A.id=table_B.id;

子查询
select * from table_A where id in (select id from table_B);

每个表中都会有主键，根据ID去除列的重复数据
select * from table_B b where not exists(select * from table_B where testCol=b.testCol and id > b.id);

日期分表查询
对于年在一百万以下的数据，可以按年分，否则还得按月分