MINUS query in MySQL
Mysql does not support MINUS operator which is not the end of the world. For most queries you can really care less if it’s implemented or not. My web app never needs to use it to function properly, which makes MySQL a perfect choice here. But, sometimes human beings are more demanding than simple php applications. Especially if you want to extract some non-obvious information from the database.
Just today I wanted to see who in our company does not have an assigned laptop. I have two tables - one is user table which contains names and info of all the employees. The other one is computer table which contains service tags of all the machines we own. You can join these two on username and you figure out which computer is assigned to who. But I wanted the opposite thing - which is a logical intersection of the two tables on username. Which is usually obtained via MINUS operator.
Of course MySQL does not have MINUS, so I spent 5 minutes staring at the screen and typing mangled SQL queries trying to extract this info. Then I decided to google for it. Here is the solution:
SELECT users.username
FROM users
LEFT JOIN computers ON computers.username = user.username
WHERE computers.username IS NULL
Duh! I feel dumb that I couldn’t figure this out myself, but I was nowhere near the solution. Anyways it’s good to know for the future.
Related Posts:
June 19th, 2007 at 8:46 am (4846) [Quote]
Thanks, I had the same problem and also just stared at the screen
Posted using