join :取两个表的合集;
left join:左表的数据全部保留,然后增加右表与左表条件匹配的记录。如下
select cc.* from cloud_groups as cg left join cloud_contacts as cc on cg.ou=cc.departmentNumber order by cast(cg.sort as int),cast(cc.sort as int) //先以部门排序,再以员工进行排序
效果:
(1)cloud_groups 的内的数据全部保留
(2)增加 cloud_contacts 表中 cg.ou=cc.departmentNumber 的记录
right join: 与left join的效果正恰相反。右表的数据全部保留,然后增加与相应条件匹配的记录。
示例:
//根据部门编码获取指定部门下员工的数量 -(NSString *)getStaffCount:(NSString *) code { NSString *strSql = [[NSString alloc]initWithFormat:@" select count(*) from(select * from cloud_contacts as cc left join cloud_groups as cg on cg.ou=cc.departmentNumber) where code like'%%%@%%'",code]; NSString *staffCount=nil; if([mysqlite openDatabase:@"ucab_db.db"]) { staffCount = [[mysqlite selectintBySQL:strSql] objectAtIndex:0]; [mysqlite closeDatabase]; } if (nil!=staffCount) { return staffCount; } else return @"0"; }
关键字:on 是两个表连接查询时的条件,用于筛选笛卡尔积得出临时表。where 是对对临时表进行再次查询!