title: How to apply the MINUS efficiently on mysql query for tables with large data
body: <p>I have 2 tables as the following - </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `nl_members` (
    `member_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `member_email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `member_confirmation_code` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
    `member_enabled` enum('Yes','No') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Yes',
    PRIMARY KEY (`member_id`),
    UNIQUE KEY `TUC_nl_members_1` (`member_email`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=58520 ;
  
  CREATE TABLE IF NOT EXISTS `nl_member_group_xref` (
    `group_id` int(10) unsigned NOT NULL,
    `member_id` int(10) unsigned NOT NULL,
    `member_subscribed` enum('Yes','No') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Yes',
    `subscribe_date` int(10) unsigned NOT NULL DEFAULT '0',
    `unsubscribe_date` int(10) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`group_id`,`member_id`),
    KEY `nl_members_nl_member_group_xref` (`member_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  
  ALTER TABLE `nl_member_group_xref`
   ADD CONSTRAINT `nl_members_nl_member_group_xref` FOREIGN KEY (`member_id`) REFERENCES `nl_members` (`member_id`),
   ADD CONSTRAINT `nl_member_groups_nl_member_group_xref` FOREIGN KEY (`group_id`) REFERENCES `nl_member_groups` (`group_id`);
  </code></pre>
  
  <p>Both has quite some large amount of data about millions of them.</p>
  
  <p>What i want is to have an efficient was of applying the MINUS on result set.</p>
  
  <p>For example, </p>
  
  <p>i want to get all the users from Group1 with ID: 1 MINUS all users from Group2 with ID: 2 and Group3 with ID: 3</p>
  
  <p>How can i do it efficiently? with the query running as fast as possible.</p>
  
  <p><strong>Update</strong></p>
  
  <p>What i want is like this - </p>
  
  <p>in members table 'nl_members' i keep a list of all members, who could have been associated with one or more groups.</p>
  
  <p>for each group association for a member there will be a row in the 'nl_member_group_xref' table.</p>
  
  <p>so if a member is associated with 3 groups there will be 3 entries in the member_group_xref table.</p>
  
  <p>Now what i want is to get all members included in group 1 but exclude members if they also belong to group 2 and group 3.</p>
  
  <p>Hope this helps.</p>