SQL from zero · Chapter 11 of 37

Nobody left behind

By the end of this chapter you can join two tables and keep every row of one of them, even the rows with no match, and find exactly the rows that have none. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Joins
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Rohan wants a list of every member with how many posts they have written, so he can nudge the quiet ones to say hello. MingleSphere has 24 members. He joined users to posts, grouped by username, and got 23 rows.

Nobody left. Yash has never posted, so a plain JOIN found no post to pair him with and dropped him. That is worse than a missing row: the members Rohan most wants to nudge are exactly the ones an inner join throws away, and nothing in the answer tells you they are gone.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    post-counts.sql: every member's post count, zeros included

    For every member, count how many posts they have written. Every member is in the list, including members who left the site, and a member with no posts shows 0. Deleted posts count too.

    Return two columns: username and posts. Any order.

  2. Task 2

    never-posted.sql: members who never posted

    List the members who have never written a post. A deleted post still counts as a post, and members who left the site are included like anyone else.

    Return one column: username. Any order.

  3. Task 3

    quiet-posts.sql: posts nobody commented on

    List the posts that are not deleted and have no comments at all, not even a reply. Show each post's author, its content and when it was written. Posts by members who left still count.

    Return three columns: username, content and created_at (the post's). Any order.

  4. Task 4 · interview question

    Interview: liked-post-counts.sql, every member's posts with 3 or more likes

    For every member, count how many of their posts have 3 or more likes (likes_count). Deleted posts count too. Every member is in the list, including members who left, and a member with no such posts shows 0.

    Return two columns: username and posts. Any order.

What you will learn

  • LEFT JOIN: keep every row of the left table, with NULLs where the right table has no match.
  • Counting after a LEFT JOIN: why count(*) says 1 where count(column) says 0.
  • The anti-join: keep only the NULL rows to find what has no match.
  • ON or WHERE: where a condition on the right table belongs.

More in Joins

Build "Nobody left behind" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.