Combining JOIN and WHERE clause. Versions in SQL and HQL

By neokrates, written on May 5, 2008

article

  • Join date: 11-30-99
  • Posts: 224
View Counter:
  • 4,252 views
Rate it
Ad
Poll
  • Which SQL implementations are mostly used in your projects?

    View Results

    Loading ... Loading ...
Feeds:
  • bodytext bodytext bodytext

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.

MYSQL Version

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.

Be Sociable, Share!
 


TAGS

SOCIAL
Be Sociable, Share!


Comments are closed.