4

I have a table containing the names and filesystem locations of C source-files in a table, a list of relevant categories for the programming principals demonstrated by the source files in a second table and then a junction table that is a one-to-many for the categories associated with each source-file. (table definitions below question)

What I need to do is get a list of file_ids from the source-file table that have no associated file_id entry in the junction table. I have done that using an except clause that relies on an inner join selecting records from the junction table where file_ids are present in the junction table, e.g.

select distinct file_id from c_file
  
  except (
    
    select distinct c_file.file_id from c_file 
      
      inner join c_file_topic_rel 
        on c_file_topic_rel.file_id = c_file.file_id 
        
        where exists (
          
          select distinct file_id from c_file_topic_rel
        
        )
  )
  
  order by file_id;

This works fine, but I'm no SQL master and I'm skeptical I'm taking a very long way around to get the records from the c_file table NOT in the junction table c_file_topic_rel. Is there a better way to go about isolating the records with no entry in the junction table? (there are about 15,000 total entries in the c_file table, if the size matters -- it's relatively small)

Table Definitions

The Default column is removed to prevent excess scrolling. If relevant, I'm happy to add the full definitions.

c_file table

                                         Table "public.c_file"
 Column  |            Type             | Collation | Nullable |
---------+-----------------------------+-----------+----------+
 file_id | integer                     |           | not null | ...
 fname   | character varying(64)       |           | not null |
 dirname | character varying(128)      |           | not null |
 mtime   | timestamp without time zone |           | not null | ...
 created | timestamp without time zone |           | not null | ...
Indexes:
    "cfile_pkey" PRIMARY KEY, btree (file_id)
    "cfile_fname_key" UNIQUE CONSTRAINT, btree (fname)

c_topics table

                                                 Table "public.c_topics"
      Column       |            Type             | Collation | Nullable |
-------------------+-----------------------------+-----------+----------+
 topic_id          | integer                     |           | not null | ...
 topic             | character varying(32)       |           | not null |
 topic_description | character varying(96)       |           | not null |
 created           | timestamp without time zone |           |          | ...
 modified          | timestamp without time zone |           |          | ...
 seq               | smallint                    |           |          |
Indexes:
    "c_topics_pkey" PRIMARY KEY, btree (topic_id)
    "c_topics_topic_key" UNIQUE CONSTRAINT, btree (topic)
Referenced by:
    TABLE "c_file_topic_rel" CONSTRAINT "c_file_topic_rel_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES c_topics(topic_id)

c_file_topic_rel junction table

           Table "public.c_file_topic_rel"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 file_id  | integer |           | not null | 
 topic_id | integer |           | not null | 
Indexes:
    "c_file_topic_rel_file_id_topic_id_key" UNIQUE CONSTRAINT, btree (file_id, topic_id)
Foreign-key constraints:
    "c_file_topic_rel_file_id_fkey" FOREIGN KEY (file_id) REFERENCES c_file_bf(file_id)
    "c_file_topic_rel_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES c_topics(topic_id)

1 Answer 1

4

It seems you are over-complicating it and joining onto C-file multiple times. You can just use a NOT EXISTS

SELECT
  f.file_id
FROM c_file f
WHERE NOT EXISTS (SELECT 1
    FROM c_file_topic_rel r
    WHERE r.file_id = f.file_id
);

DISTINCT is not necessary on the outside as file_id is already unique. On the inner query we are using EXISTS so DISTINCT is pointless.


Other equivalent syntaxes (these should have the same performance)

SELECT
  f.file_id
FROM c_file f
EXCEPT
SELECT r.file_id
FROM c_file_topic_rel r;
SELECT
  f.file_id
FROM c_file f
WHERE f.file_id NOT IN (
    SELECT r.file_id
    FROM c_file_topic_rel r
);

I would advise not to ever use NOT IN as it can give incorrect results in the presence of nulls.

Another, usually slower, syntax

SELECT
  f.file_id
FROM c_file f
LEFT JOIN c_file_topic_rel r ON r.file_id = c.file_id
WHERE r.file_id IS NULL;
2
  • Hallelujah! I knew I was making it way harder than necessary. I tried where not exists but never said the incantation just right. Thank you immensely. One of these days my SQL foo will less awkward -- when the magic of experience does its work. Jun 5 at 16:31
  • In SQL when in doubt always express the query in the most natural way possible. Jun 5 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.