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
精彩评论