MySQL: Using sub-selects to filter results of one table that is not contained in another table

In MySQL, if you have two tables with a shared index and you would like to show the results of one table where the index is not within another table, you can use “NOT IN”.

Try Googling “MySQL NOT IN” and you get everything but this command, it’s almost as if they need to have an alias for this simple command since these two words are in EVERYTHING and on so many web pages. Perhaps “NOTCONTAINEDIN” would be better, all in one word? Just a suggestion.

In the sub-select, the query inside parenthesis, you can take this a step further and apply conditions to the filter so you can exclude results in “table2” more narrowly than just being in “table1”, perhaps if “table1” had a status of “EXPIRED” in a field, then you could add that criteria to be more selective.

Aaron Belchamber has written 243 articles

Leave a Reply

Your email address will not be published. Required fields are marked *