CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8995

Get rows that no foreign keys point to

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.

Upvotes: 11

Views: 3117

Answers (4)

Koen.
Koen.

Reputation: 27039

I believe NOT IN could be used here as well, although this might be less performant on large datasets:

SELECT * 
FROM city_url
WHERE id NOT IN (
  SELECT city_url_id FROM email_account
)

Upvotes: 1

Alkis
Alkis

Reputation: 3

A NOT EXISTS is absolutely the answer to the "... if there is no row ...".
Nonetheless it would be preferable to accomplish this by selecting then difference quantity.

Which is in principle:

SELECT a.*  
FROM table1 a
LEFT JOIN table2 b
ON a.[columnX] = b.[columnY]
WHERE b.[columnY] IS NULL

Using the tablenames here, this would be:

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

Upvotes: 0

Edson Horacio Junior
Edson Horacio Junior

Reputation: 3143

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271211

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
                 );

Upvotes: 11

Related Questions