publicvoidleftJoin(int poetId){ // left join, 以左表为主,右表显示与左表有交集的数据,若不存在,使用null填充(若左表不存在,右表有数据,则不展示) // select * from poet left join poetry on poet.id=poetry.poet_id where poet.id=xxx Result<Record> record = dsl.selectFrom(poetTable.leftJoin(poetryTable).on(poetTable.ID.eq(poetryTable.POET_ID))).where(poetTable.ID.eq(poetId)).fetch(); System.out.println(">>>>>>>>> left join <<<<<<<<<<"); System.out.println(record); }
publicvoidrightJoin(int poetId){ // right join, 以右表为主,左表显示与右表有交集的数据,若不存在,使用null填充(若右表不存在数据,左表有数据,则不展示) // select * from poet right join poetry on poet.id=poetry.poet_id where poetry.poet_id=xxx Result<Record> record = dsl.selectFrom(poetTable.rightJoin(poetryTable).on(poetTable.ID.eq(poetryTable.POET_ID))).where(poetryTable.POET_ID.eq(poetId)).fetch(); System.out.println(">>>>>>>>> right join <<<<<<<<<<"); System.out.println(record); }
4. union & union all
UNION 操作符用于合并两个或多个 SELECT 语句的结果集,请注意union会对结果进行去重,而union all会显示所有的结果
1 2 3 4 5 6 7 8 9 10 11
publicvoidunion(){ // union 联合,特点是会去重重复的数据 // select id from poet union select poet_id from poetry Result<Record1<Integer>> res = dsl.select(poetTable.ID).from(poetTable).union(dsl.select(poetryTable.POET_ID).from(poetryTable)).fetch(); System.out.println(">>>>>>>>> union <<<<<<<<<<"); System.out.println(res);
System.out.println(">>>>>>>>> union all <<<<<<<<<<"); res = dsl.select(poetTable.ID).from(poetTable).unionAll(dsl.select(poetryTable.POET_ID).from(poetryTable)).fetch(); System.out.println(res); }