MySQL笔记9

1.Left and Right joins

'''
A right outer join will include all of the rows of the table to the right of the RIGHT JOIN clause.
'''
%%sql
SELECT r.dog_guid AS rDogID, d.dog_guid AS dDogID, r.user_guid AS rUserID, d.user_guid AS dUserID, AVG(r.rating) AS AvgRating, COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type
FROM dogs d RIGHT JOIN reviews r
  ON r.dog_guid=d.dog_guid AND r.user_guid=d.user_guid
WHERE r.dog_guid IS NOT NULL
GROUP BY r.dog_guid
HAVING NumRatings >= 10
ORDER BY AvgRating DESC
LIMIT 10;

Example2

%%sql 
SELECT DISTINCT u.user_guid AS UserID, COUNT(d.dog_guid) AS Numberdogs
FROM users u LEFT JOIN dogs d
ON u.user_guid = d.user_guid
GROUP BY UserID 
ORDER BY Numberdogs DESC
LIMIT 10;

Example3:

'''
Question 10: How would you write a query that used a left join to return the number of distinct user_guids that were in the users table, but not the dogs table (your query should return a value of 2226)?
'''
%%sql 
SELECT  COUNT(DISTINCT u.user_guid) AS Number
FROM  users u LEFT JOIN  dogs d
ON u.user_guid = d.user_guid
WHERE d.user_guid IS NULL;

2.关于 COUNT DISTINCT 的注意点。

'''
That's because COUNT DISTINCT does NOT count NULL values, 
while SELECT/GROUP BY clauses roll up NULL values into one group. 
If you want to infer the number of distinct entries from the results of a query using joins and GROUP BY clauses,
 remember to include an "IS NOT NULL" clause to ensure you are not counting NULL values
'''