This lesson's interactive features are locked, please to keep using them

Left Join

A LEFT JOIN will return every record from table_a regardless of whether or not any of those records have a match in table_b. A left join will also return any matching records from table_b. Here's a Venn diagram to help visualize the effect of a LEFT JOIN:

A small trick you can do to make writing the SQL query easier is to define an alias for each table. Here's an example:

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id;

Notice the simple alias declarations e and d for employees and departments, respectively.

Some developers do this to make their queries less verbose. That said, I personally hate it because single-letter variables are harder to grok, so don't use them in this course!

Assignment

The CashPal team needs a report on all the transactions a user has made. Join the users and transactions tables on users.id and transactions.user_id.

Your query should return the following 3 fields:
1. A user's name, as name
2. The sum of all their transaction amounts, as transaction_sum
3. The count of all their transactions, as transaction_count
Group the data by the user's id.
Order the data by the sum field in descending order.
Be sure to still return user records for users who have no transactions.
Do not alias the tables; this will fail the tests.