Building a SQL query for this

Question

I am trying to do the following but I dont have enough experience with MySQL. Would it be possible for someone to tell me what the query for this will be.

I have a databse with 2 tables

• Brief
• Info

Brief and Info have various fields but I am interested in only ID field.

This is what I am trying to do.

I want to go through all the IDs from Brief and cross reference them with all the IDs that exists in Info and only get the ones that exist in Info but dont exist in Brief

Thanks

Answer 1

SELECT i.ID
FROM Info i 
  LEFT JOIN Brief b USING(ID)
WHERE b.ID IS NULL

Alternatively:

SELECT i.ID
FROM Info i
WHERE NOT EXISTS (
  SELECT 1
  FROM Brief b
  WHERE b.ID = i.ID
)

See these 2 links for performance comparisons:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL
• LEFT JOIN / IS NULL vs. NOT IN vs. NOT EXISTS: nullable columns

Answer 2

SELECT ID FROM INFO 
   WHERE ID NOT IN 
   (SELECT ID FROM BRIEF);