HQL notes

Source 

Case IN-sensitive

except names of classes and their properties
- Doubt: "This manual uses lowercase HQL keywords. Some users find queries with uppercase keywords more readable, but this convention is unsuitable for queries embedded in Java code." - why is this so? 

the from clause

minimum query is from Student - selects all instances of Student class. By default auto-import is true. So class names do not have to be qualified with the package name. If auto-import is turned off (false) then this query must be written as from com.xyz.Student 

To refer to Student in other parts of the query, an alias must be assigned to Student. from Student as student assigns student as an alias for Student. The "as" is optional. So the previous alias association can be written as from Student student. Its a good practice to start alias names in lower case as this is in-line with java coding standards for naming variable names.
- from Student as student, Department as department gives a cartesian product of all students and all departments. 

Joins

SQL Joins

Source. Joins used to fetch rows from 2 or more tables based on the relationship between columns in both the tables. Primary key is a column (or a group of columns - composite key) with unique values that identify each row. 

1. inner join - returns a row when there is a match in both the tables
2. left outer join - returns all rows from the left table even if there are no matches on the right table
3. right outer join - returns all rows from the right table even if there are no matches on the left table
4. full join - returns rows when there is a match in one of the tables

examples:

Employee table:

emp_no	emp_name	dept_no
1	xyz	1
2	abc	2
3	ghi	4

Department table:

dept_no	dept_name
1	IT
2	Sales
3	Accounts

inner join:

select emp.emp_no, emp.emp_name, dept.dept_name from employee as emp inner join department as dept on emp.dept_no = dept.dept_no;

emp_no	emp_name	dept_name
1	xyz	IT
2	abc	Sales

left outer join

select emp.emp_no, emp.emp_name, dept.dept_name from employee as emp left join department as dept on emp.dept_no = dept.dept_no;

emp_no	emp_name	dept_name
1	xyz	IT
2	abc	Sales
3	ghi

right outer join

select emp.emp_no, emp.emp_name, dept.dept_name from employee as emp right join department as dept on emp.dept_no = dept.dept_no;

emp_no	emp_name	dept_name
1	xyz	IT
2	abc	Sales
		Accounts

full join

select emp.emp_no, emp.emp_name, dept.dept_name from employee as emp full join department as dept on emp.dept_no = dept.dept_no;

emp_no	emp_name	dept_name
1	xyz	IT
2	abc	Sales
3	ghi
		Accounts

HQL Joins

Same types of joins as SQL

1. inner join e.g. from Employee as emp inner join | join emp.department as dept

2. left outer join e.g. from Employee as emp left outer join | left join emp.department as dept

3. right outer join e.g. from Employee as emp right outer join | right join emp.department as dept

4. full join

-


Mapping reference