SQL snippet - Selecting from table a items that aren't in table b

How I found items that were present in one table but not another using SQL

Today I needed to select something from a table on the condition that the ID of the row in the table did not appear in another table.

I initially had to do this for a MySQL project, but it would equally work in Postgres or any SQL database.

Initially I thought this was going to be slightly difficult to do, or at least a bit convoluted but it turned out to not need that much code.

  SELECT table_a.id
  FROM table_a
  LEFT JOIN table_b ON table_a.id = table_b.aid
  WHERE table_b.aid IS NULL;

Since you are using a LEFT JOIN table_b.aid can return NULL values, and these are the ones you are looking for.

Recent posts View all

JavaScript

Setting a more specific database type in Prisma

Prisma sets a default of TEXT for all String fields, here is how you can override that.

Ruby

Override database attribute types

Sometimes you don't have control over how your database handles information, so you need Rails to set it