How to SQL join two tables so that all entries of the left one are in the result even if there is no matching entry in the right one?

Question

Hi everybody,

I have spent several hours with this SQL problem, which I thought would be easy - I still think it should be, but obviously not easy enough for me (not an SQL expert). I would be glad if you could help me with this.

I have stripped this down for this example. Imagine two tables:

PRODUCT
 -ID
 -NAME

REVIEW
 -ID
 -PRODUCT_ID
 -USER_ID
 -CONTENT

Where each user can review a product only once.

What I need now is an SQL query that returns all products together with the reviews of a given user. If a user has not reviewed a product, there should be NULL values returned for the review columns.

I thought something like the following would do, but obviously it does not, since only reviewed products are returned.

SELECT p.*, r.* 
from PRODUCT p join REVIEW r 
  on r.PRODUCT_ID = p.ID 
where r.USER_ID=:userId

I would be veeeeery happy if someone could help me out with this. I am pretty sure there has to be some kind of subselect involved, but I do not seem to be able to figure it out myself :(

BTW: I need to translate this to HQL, so the solution should not make use of features like UNION, which are not supported in HQL.

Thanks a lot & best regards, Peter

Answer 1

SELECT p.*, r.* 
 from PRODUCT p LEFT JOIN REVIEW r 
  on r.PRODUCT_ID = p.ID 
 where r.USER_ID=:userId

Answer 2

If you want to select products where a no users have created a review:

SELECT p.*, r.* from PRODUCT p join REVIEW r on r.PRODUCT_ID = p.ID
WHERE r.USER_ID IS NULL

If you want to select products where a specific user hasn't created a review:

SELECT p.*, r.* from PRODUCT p join REVIEW r on r.PRODUCT_ID = p.ID
WHERE r.USER_ID IS NULL or r.USER_ID!=:userId

Answer 3

Ah, just after posting this, I have finally found the answer on another site. (I have searched before, I swear ;-)

The trick is not to have the userId restriction in a WHERE clause, but rather as part of the JOIN:

SELECT p.*, r.* 
from PRODUCT p LEFT JOIN REVIEW r 
  on r.PRODUCT_ID = p.ID AND r.USER_ID=:userId

Thanks for your thoughts!

(edited: also have to use a LEFT join, as pointed out by Martin Smith, thanks!)

Answer 4

To get the effect you described, you should use a left outer join, but constrain your right-hand side by either a matching user ID or null.

SELECT p.*, r.* 
 from PRODUCT p LEFT OUTER JOIN REVIEW r 
  on r.PRODUCT_ID = p.ID 
 where r.USER_ID=:userId or r.USER_ID is null

Answer 5

Another aproach using subqueries

SELECT P., R. FROM PRODUCT P LEFT JOIN (SELECT * FROM REVIEW WHERE UserId=:userId) UR ON UR.ProductId=P.ID