Search code examples
mysqlsubtractionexcept

MySQL - subtracting one query set from another

I'm not sure how to do this as my lecture notes tell me to use "EXCEPT" in SQL but MYSQL does not seem to support this. I've been thinking I should use "WHERE NOT IN" as the alternative, but my problem is that both of these columns I need to compare have different names.

Here is my incorrect query:

(SELECT id FROM projects) WHERE id NOT IN  (SELECT project_id FROM projects_viewed);

I want to select all rows from the table "projects" where the id column does NOT appear in the "projects_viewed" table. But the column is called "project_id" instead of "id" because it's a foreign key.

Basically the logic behind this is that the result set returned from this query should be all the project id's that have not been viewed (the ones in the 2nd table).

Solution

  • There are several ways to do this. Here's one using an outer join with a null check:

    select p.id
from projects p
   left join projects_viewed pv on p.id = pv.project_id
where pv.project_id is null

    And another with not in:

    select id 
from projects 
where id not in (select project_id from projects_viewed)

    I think these methods are more efficient in mysql than not exists.