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?

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 solu开发者_如何学Gotion should not make use of features like UNION, which are not supported in HQL.

Thanks a lot & best regards, Peter

---

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

---

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!)

---

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

---

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

---

Another aproach using subqueries

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