Tag: mysql query

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

03/16/2015 Aaron Belchamber No Comments

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”.

SELECT * FROM table2 WHERE some_field not in 
    (SELECT some_field FROM table1)

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.


MySQL: Exclude ‘group by’ group if field A in any row has value B with “Natural Join”

12/21/2014 Aaron Belchamber No Comments

Querying databases get a little tricky when you have a stack of records associated with another table, perhaps a master table of accounts, and you’re trying to filter out values that would exclude the results from another group of results.
In this example below, we are getting a list of organization IDs for all countries who DO NOT have any address in the country Uzbekistan. Not picking on you Uzbekistan, just using your country name as an example…

SELECT o.org_id
FROM organization o
NATURAL JOIN addresses a
WHERE NOT EXISTS 

   (SELECT * FROM addresses a1 
    WHERE o.org_id = a1.org_id AND a1.country = 'Uzbekistan')

GROUP BY o.org_id

This kind of filtering gets tricky, unless it’s clearly known the database tables are optimized, joins can bring your server down, so test it on a dev site and be prepared to stop the process on the server. It’s almost always safer to pre-qualify the list then iterate through and sort, though this way is slower, obviously, it’s not prone to slowing down your database performance.

Also, according to MySQL, there are some other basic guidelines to consider when using natural joins:

– The associated tables have one or more pairs of identically named columns with the SAME DATA TYPE.
– Do not use ON clause in a natural join.