数据检索-多表查询(sybase)

数据检索-多表查询
chinayaosir 12/22/2008

//1.single select sentence
//1.单表查询
//1.1 single select sentence with table i_itembasic,then run value is TotalRows:12695
//primary key is itemnumber
select count(*) as TotalRows
from i_itembasic;
/*
run value:
TotalRows
12695
*/

//1.2 single select sentence with table i_itembasic_bab,then run value is TotalRows:12634
//primary key is itemnumber
select count(*) as TotalRows
from i_itembasic_bab;
/*
run value:
TotalRows
12634
*/

//2.inner join select sentence(select * form table1,table2...)
//2.内连接
select count(*) as TotalRows
from      i_itembasic,i_itembasic_bab
where  i_itembasic.itemnumber = i_itembasic_bab.itemnumber;



//3.outer join
//3.外连接

//3.a right outer join select sentence(=*)
//3.a 右外连接
//3.a.1 primary table choose i_itembasic_bab,slave table choose i_itembasic
select count(*) as TotalRows
from      i_itembasic,i_itembasic_bab
where  i_itembasic.itemnumber =* i_itembasic_bab.itemnumber;
/*
run value:
TotalRows
12634
*/

//3.a.2 primary table choose i_itembasic,slave table choose i_itembasic_bab
select count(*) as TotalRows
from      i_itembasic,i_itembasic_bab
where  i_itembasic_bab.itemnumber =* i_itembasic.itemnumber ;
/*
run value:
TotalRows
12695
*/

/*
why?3.a.1(TotalRows:12634) <>3.a.2(TotalRows:12695)
pls thinking...
then study right outer join with www.google.cn or www.baidu.com
then you can see
*/

//3.b left outer join select sentence(*=)
//3.b 左外连接
//3.b.1 primary table choose i_itembasic,slave table choose i_itembasic_bab
select count(*) as TotalRows
from      i_itembasic,i_itembasic_bab
where  i_itembasic.itemnumber *= i_itembasic_bab.itemnumber;
/*
run value:
TotalRows
12659
*/

//3.b.2 primary table choose i_itembasic_bab,slave table choose i_itembasic
select count(*) as TotalRows
from     i_itembasic,i_itembasic_bab
where  i_itembasic_bab.itemnumber *= i_itembasic.itemnumber ;
/*
run value:
TotalRows
12634
*/

/*
why?3.b.1(TotalRows:12695) <>3.b.2(TotalRows:12634)
pls thinking...
then study left outer join with www.google.cn or www.baidu.com
then you can see
*/

//4.cross select sentence(=)
//交叉查询两表之间的笛卡尔乘积
select count(*) as TotalRows
from      i_itembasic,i_itembasic_bab
where  i_itembasic.itemnumber = i_itembasic_bab.itemnumber;

/*
run value:
TotalRows
12632
*/

/*
why ?12632 <>12695<>12634
pls thinking...
then study left outer join with www.google.cn or www.baidu.com
then you can see
*/