Get rows that no foreign keys point to

Question

I have two tables

CREATE TABLE public.city_url
(
  id bigint NOT NULL DEFAULT nextval('city_url_id_seq'::regclass),
  url text,
  city text,
  state text,
  country text,
  common_name text,
  CONSTRAINT city_url_pkey PRIMARY KEY (id)
)

and

CREATE TABLE public.email_account
(
  id bigint NOT NULL DEFAULT nextval('email_accounts_id_seq'::regclass),
  email text,
  password text,
  total_replied integer DEFAULT 0,
  last_accessed timestamp with time zone,
  enabled boolean NOT NULL DEFAULT true,
  deleted boolean NOT NULL DEFAULT false,
  city_url_id bigint,
  CONSTRAINT email_accounts_pkey PRIMARY KEY (id),
  CONSTRAINT email_account_city_url_id_fkey FOREIGN KEY (city_url_id)
      REFERENCES public.city_url (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

I want to come up with a query that fetches rows in the city_url only if there is no row in the email_account pointing to it with the city_url_id column.

Answer 1

NOT EXISTS comes to mind:

select c.*
from city_url c
where not exists (select 1
                  from email_account ea
                  where ea.city_url_id = c.id
                 );

Answer 2

There's also this option:

SELECT city_url.*
FROM city_url
LEFT JOIN email_account ON email_account.city_url_id = city_url.id
WHERE email_account.id IS NULL