Combining JOIN and WHERE clause. Versions in SQL and HQLBy neokrates, written on May 5, 2008
Is it possible to combine JOIN and WHERE clause in the same query? In SQL- yes, absolutely. In Hibernate’s HQL it is also possible, but there are problems to overcome. There is also a special case of JOIN ON in Hibernate. In time this article is written, those Hibernate problems have no simple solution. To overcome JOIN ON limitation, one can use sub-selects, similar to SQL. The overhead, caused by that kind of additional queries seems to be limited.
I will use MySQL as SQL platform in this example.
Why combine ‘JOIN’ and ‘WHERE’ in one query?
WHERE is used to limit the return data by applying a limiting criteria.
JOIN can add the data, using logic similar to the set union (intersection, unique element, etc…)
Here is one very nice explanation of the JOINS: http://www.codinghorror.com/blog/archives/000976.html
So, to add some data to the response, but only if the data is there, and return the data only for specific cases, you will probably combine JOIN and WHERE.
Suppose, you won’t to have information about all orders from new customers, registered during last 10 days. You also would like to have the abrogation information, if the order was called back.
Here is the example:
SELECT c.customerId, c.customerName, o.orderId, o.price, a.abrogationSummaryInfo FROM (Customer c, Orders o) LEFT JOIN Abrogation a ON (a.orderId = o.orderId) WHERE TO_DAYS(c.customerRegistrationDate) > TO_DAYS(CURDATE())-10 AND o.customerId=c.customerId
This WHERE and JOIN combination should work on the current MySQL systems. There is one important thing to mention. On older MySQL and SQL systems, the syntax was a bit different. If you port you queries from the other system you will probably have FROM Customer c, Orders o , i.e. without () and that will cause the following error:
1054, Unknown column ‘column name’ in ‘on clause’
As of MySQL 5.0.12, natural joins and joins with "USING" keyword, including outer join variants, are processed according to the SQL:2003 standard. This change may necessitate that certain queries be rewritten.
HQL Version of the query
General information about HQL can be read at http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html.
To put it simple, WHERE with JOIN ON will not work as it is (currently). Hibernate does support JOIN, but has troubles with joining ON some key column of the table without having explicit hibernate mapping.
Here is bit more on the nature of the problem: http://forum.hibernate.org/viewtopic.php?t=971401&highlight=join+using+clause
I know of 3 ways to overcome this shortcoming:
- Use SQL directly from the hibernate, so, basically, the query from previous chapter;
- Declare the mapping in the regular hibernate way;
- Use sub-SELECTS to retrieve the data:
SELECT c.customerId, c.customerName, o.orderId, o.price, (SELECT a.abrogationSummaryInfo FROM Abrogation a WHERE (a.orderId = o.orderId)) FROM (Customer c, Orders o) WHERE TO_DAYS(c.customerRegistrationDate) > TO_DAYS(CURDATE())-10 AND o.customerId=c.customerId
It will return NULL for a.abrogationSummaryInfo if it finds nothing. In my experience Hibernate performs ok with many such sub queries.