From

[View full width]
FROM className AS? identifier (  ( COMMA className AS? identifier ) | ( joinType classname
 AS? identifier ) )*

An identifier must be assigned to the class for queries that need to refer to a class property. For example, com.cascadetg.ch04.Student is typically assigned an identifier such as student. This identifier is required in order to refer to the class in other clauses of the query. For example, select first Name from Student is invalid HQL; the valid statement would be select student.firstName from Student as student.

Figure 8.5. Student Data

mysql> select * from student;
+----+-----------+----------+-------------+
| ID | firstName | lastName | idString    |
+----+-----------+----------+-------------+
|  1 | Bob       | Smith    | 123-45-6789 |
|  2 | John      | Stevens  | 456-78-9012 |
|  3 | Betty     | Almara   | 098-76-5432 |
+----+-----------+----------+-------------+
3 rows in set (0.00 sec)

The identifier is particularly useful when you are writing a complex query that needs to refer to the same table with two different meanings; for example, if you wish to join a class against itself.

The AS token is optional, but some prefer it to enhance readability. The statements select owner from Owner as owner and select owner from Owner owner are equivalent.

The values of the joinType token are as shown below.

Join Types

( ( 'left'|'right' 'outer'? ) | 'full' | 'inner' )? JOIN FETCH?

The join syntax is used to control the product of the result of joining two tables together. To consider the results of the various types of join, let's start with a bit of data as shown in Figures 8.5 and 8.6.

Figure 8.6. Exam Result Data

mysql> select * from examresult;
+----+-------+-----------+--------+
| ID | score | studentID | examID |
+----+-------+-----------+--------+
|  1 |    85 |         1 |      1 |
|  2 |    89 |         2 |      1 |
|  4 |    75 |         1 |      2 |
|  5 |    83 |         2 |      2 |
+----+-------+-----------+--------+
4 rows in set (0.01 sec)

Looking at the data, we see that there are three students, but only students 1 and 2 have actually taken any exams. Joins allow us to glue together the data items from Figure 8.5 and Figure 8.6 in different ways to obtain different results (the heart of the notion of a relational database).

Inner Join

From basic SQL, we know that the query select result, student from Student as student, Examresult as result will result in a multiplied set of returned data, as shown in Figure 8.7. This is what is referred to as an inner join.

Figure 8.7. Unrestricted Inner Join Results

[View full size image]

A where clause is used to cull the data down. The statement select result, student from Student as student, Examresult as result where result.student=student restricts our query to the students who have taken exams, as shown in Figure 8.8.

Figure 8.8. Bound Inner Join Results

[View full size image]

Inner joins are the most basic form of join, and the easiest to understand.

Left/Right Joins

Now, imagine that we wish to retrieve all of the students even if they haven't taken a testin other words, return the records even of a student for whom there are no examresult records. We have to use an outer join to retrieve all of the records. Outer joins come in two flavors, right join and left join. The use of the right or left term depends on the table you wish to use as the "master" table.

The statement select result, student from Student as student, Examresult as result right join result.student joinedstudent where student=joinedstudent returns all the students, including the student with a null examresult as shown in Figure 8.9.

Figure 8.9. Right Outer Join

[View full size image]

The join is performed and retrieves all of the records from the student table, even though the record may not be present in the right table (the student table). In effect, you are asking for all records from the right table, in this case student, even if there are no corresponding entries present in the examresult table.

Conversely, using the left join statement instead, as in select result, student from Student as student, Examresult as result left join result.student joinedstudent where student=joinedstudent returns the joined records from the perspective of the examresult table (the left table), as shown in Figure 8.10. In effect, you are asking for all of the records in the examresult table, even if not present in the student table. As there are no records in the examresult table that do not point to a student (that would be a foreign-key violation), you simply get the exam results.

Figure 8.10. Left Outer Join

[View full size image]

Full Joins

A full join will return all records from both tables, regardless of any matches between the tables. Note that this command is not supported on all databases (in particular, MySQL doesn't offer support for the full join command).

Outer as Inverse of Inner

It's worth pointing out that you may be specifically seeking the records that do not match a regular join. For example, you may only be interested in the students who haven't taken an exam. To perform this query, you can use the is null modifier, as in select result, student from Student as student, Examresult as result right join result.student joinedstudent where student=joinedstudent and result is null, which produces the results shown in Figure 8.11.

Figure 8.11. Using an Outer Join to Obtain the Inverse of an Inner Join

[View full size image]

Fetch Joins

The fetch join is a useful shortcut used to fetch parent and child objects in a single select. For example, the statement select student from Student as student left join fetch student.examresults returns all of the students and their Examresult objects, regardless of whether the student has any associated exam results. If there are any examresults, they are already loaded into Examresult objects, associated with the Student object (accessible via the Student.getExamresults() method. If you know that you will be reading the exam results of the student objects, but have the exam result relationship set to lazy by default, this is an excellent way to retrieve all of the needed data in a single statement.

Similarly, the statement select distinct student from Student as student inner join fetch student.examresults retrieves the student objects with an exam record. As in the left join fetch, the Student and Examresult objects are loaded, but only students with an Examresult are returned.

The fetch statement may only be used as an inner join or a left outer join.

As of the release of Hibernate Extensions-2.1, the Hibern8 IDE does not support the fetch statement. To test fetch joins, you'll need to use the HQL in your own sample code.