Let's say I have the following query to list the average value of a house people own:
SELECT PERSON.NAME, AVG
FROM PERSON, (
SELECT HOUSE.PID AS PID, AVG(HOUSE.VALUE) as AVG
GROUP BY PID
WHERE PERSON.PID = HOUSES.PID OR PERSON.ID NOT IN (
Simple rule: Never use commas in the
FROM clause. Always use explicit
JOIN syntax. For instance, in this case, you want a
LEFT JOIN, but cannot express it.
SELECT p.NAME, AVG_VALUE FROM PERSON p LEFT JOIN (SELECT PID , AVG(VALUE) as AVG_VALUE FROM HOUSE GROUP BY PID ) h ON p.PID = h.PID;
If you want
0 instead of
COALESCE(AVG_VALUE, 0) as AVG_VALUE in the outer query.