HQL的左连接_左连接与右连接的区别

全栈程序员-用户IM • 2022年9月15日下午6:16 • 未分类

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元售后保障童叟无欺

最近做一个查询实现把一个表的记录全部显示出来并且显示关联的另外一个表的记录，这当然谁都知道要用到外连接查询，然而过程并不愉快。

在Hibernate的映射文件中配置好关联关系之后，查询的时候可以直接使用比如

select new map(student.studentID as studentID, student.studentAccount as studentAccount,student.studentName as studentName,student.skill.skill as skill) from Student student where student.studentID!=0 and ........

，但是默认使用的内连接，就是说外键必须匹配的记录才能查出来，实现不了要求。
当我决定用左连接查询之后，做了很多尝试，但是因为对HQL不够熟悉，都没有达到要求。比如这样的

select new map(student.studentID as studentID,student.studentAccount as studentAccount,student.studentName as studentName,student.canInterview as canInterview,student.mail as mail,skill.skill as skill,skill.description as description) from Student student left join student.skill skill with student.studentID!=0 and ........
错误，报错：with-clause expressions did not reference from-clause element to which the with-clause
原因因给是是在with语句里面没有用到skill

改成是这样？

select new map(student.studentID as studentID,student.studentAccount as studentAccount,student.studentName as studentName,student.canInterview as canInterview,student.mail as mail,skill.skill as skill,skill.description as description) from Student student left join student.skill skill where student.skill.skillID = skill.skillID and ....... 虽然有了skill，但是，报错：with-clause referenced two different from-clause elements

幸好没有崩溃，想起来看一下书。其实怪就怪在没想起来用到join…where，where对字段的限制并没有那么严格，但是因为在Student关联的是Skill实体，又不能直接用where而放弃join，所以，正确的语句：

select new map(student.studentID as studentID,student.studentAccount as studentAccount,student.studentName as studentName,student.canInterview as canInterview,student.mail as mail,skill.skill as skill,skill.description as description) from Student student left join student.skill skill where student.studentID!=0 and ......