join query does not return null values

January 9, 2022

I have this table with units/homes

CREATE TABLE `units` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `cancellation_policy` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `image_url` varchar(255) NOT NULL,
  `price` decimal(19,2) NOT NULL,
  `region` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UKha7gwhuig6p6vftvoghfi2b7g` (`title`,`image_url`),
  UNIQUE KEY `UK_pdd7pto9vch2kb58kohy96a5f` (`image_url`),
  UNIQUE KEY `UK_58rre8c1gk28a7d5p6wguiti9` (`title`)
)

and this one with reviews

CREATE TABLE `reviews` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  `stars` int NOT NULL,
  `user_id` varchar(255) NOT NULL,
  `unit_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UKc3rd8vjkpbcda34jomifuybu9` (`user_id`,`unit_id`),
  KEY `FKbgbdator49pjrbriaktrbv1q2` (`unit_id`),
  CONSTRAINT `FKbgbdator49pjrbriaktrbv1q2` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`)
)

and I want to get all movies together with the average rating. However this query does not return me back movies without ratings (NULL) values. This query does not return movies without ratings

select 
u.id, u.cancellation_policy, u.description, u.image_url, u.price, u.region, u.title, round(avg(stars),0) as ratings 
from units u
inner join
reviews r
ON u.id = r.unit_id 
group by u.id

What is the correct way to get all movies including those w/o stars?

>Solution :

A LEFT JOIN would show oyu all units even those that have no reviews

SELECT 
    u.id,
    u.cancellation_policy,
    u.description,
    u.image_url,
    u.price,
    u.region,
    u.title,
    ROUND(AVG(IFNULL(stars,0)), 0) AS ratings
FROM
    units u
        LEFT JOIN
    reviews r ON u.id = r.unit_id
GROUP BY u.id