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.

This entry was posted in programming and tagged . Bookmark the permalink.



8 Responses to MINUS query in MySQL

  1. Thanks, I had the same problem and also just stared at the screen ;)

    Reply  |  Quote
  2. Touseef Zafar PAKISTAN Google Chrome Windows says:

    yeah…thats a very nice solution to this problem…
    very good
    :)

    Reply  |  Quote
  3. Very nice, exact solution that I was looking for. It is a simple solution yet difficult unless you look at the problem in different way. Good job.

    Reply  |  Quote
  4. Jamison Dance UNITED STATES Safari Mac OS says:

    Thank you google, for taking me here, and thank you sir for answering my question. You just saved me bunches of time.

    Reply  |  Quote
  5. Vardhan INDIA Google Chrome Windows says:

    Hey What is the need of ‘WHERE computers.username IS NULL’ here

    Reply  |  Quote
  6. NdR INDONESIA Opera Windows says:

    Good solve! but I have problem, how to select that with a criteria e.g where id=something?

    Reply  |  Quote
  7. Table 1 is a roster of Bowlers who can belong to one or many bowling centers. Table 2 is a roster of bowlers signed up for an event today. I list my bowling center roster with all names for that bowling center who are elegible to signup for the event roster (table 2) It is possible to signup bowlers at different times. After I signup a bowler and close the signup page, when I go back to the signup page I do not want to see the bowlers who have already signed up.

    I have tried many sql select statements, none of which produce the desired result:

    1) SELECT DISTINCT BH.BID
    FROM BOWLERS_HOUSE BH
    LEFT JOIN EVENT_ROSTER ER
    ON ER.BID = BH.BID
    WHERE ER.HOUSE_ID = 40
    AND ER.EVENT_ID = 201007073010100101010300
    AND ER.BID IS NULL; Note: returns empty result

    2) SELECT DISTINCT BH.BID
    FROM BOWLERS_HOUSE BH
    WHERE BH.HOUSE_ID = 40
    AND (BH.BID) NOT IN
    (“SELECT ER.BID FROM EVENT_ROSTER ER WHERE EVENT_ID = 201007073010100101010300 AND ER.HOUSE_ID = 40″);Note returns all BID’s in Table 1

    3) SELECT BOWLERS_HOUSE.BID
    FROM BOWLERS_HOUSE LEFT JOIN EVENT_ROSTER ON BOWLERS_HOUSE.BID=EVENT_ROSTER.BID
    WHERE EVENT_ROSTER.BID IS NULL;
    Note: returns the bowlers who have aready signed up and are on Table 2, I want only the bowlers who have not ALREADY signed up that are NOT ON table 2

    I can run the program the way it is or I can build a very convoluted compare routine to eliminate the Table2 records from my display of the House roster, but need something that works. Oh, I tried SQL MINUS, but of course mysql disregards this very useful tool.

    Any ideas?
    Thanks in advance!

    Reply  |  Quote
  8. Szabolcs Hodosi HUNGARY Google Chrome Ubuntu Linux says:

    Didn’t it easier to use outer joins instead of left/right inner join with allowing NULL values explicitly? In Database theory the outer join is for this purpose, otherwise the article is good.

    Reply  |  Quote

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>