Databases Querying Relational Databases Set Operations Except Operations

Andrew Blackwell
Andrew Blackwell
13,264 Points

Add other columns to EXCEPT query?

This is a theoretical question, but in the video Andrew Chalkley demonstrates that it is not possible to Select the ID column in the EXCEPT query, as it would be taken into account in the exception logic and compared to the id column in the second part of the query. My question is: how do I go about it if I DO want to add other columns to the query result (e.g. the id column)? Is there any short way of going about this, or do I have to do some kind of compound query (e.g. where I nest the EXCEPT query inside a larger query with an IN operator)?

2 Answers

Steven Parker
Steven Parker
201,933 Points

If you want other columns, a subquery or JOIN might be useful.

But you would not need EXCEPT:

SELECT ForeignMakeID, MakeName FROM ForeignMake
WHERE MakeName NOT IN (SELECT MakeName FROM Make);
Andrew Blackwell
Andrew Blackwell
13,264 Points

Thanks, Steven - that solves it :-)