I'm the kind of guy who believes in keeping as much logic as you can inside the database server. My standard practice for data-driven websites is to never write any SQL (or LINQ, etc.) in my .NET code ...

RIGHT JOIN (or RIGHT OUTER JOIN): It’s the opposite of a LEFT JOIN. It keeps all rows from the right table and matches from the left. Less common than LEFT JOIN, but good to know it exists.