12849

How to select all the 'not' part against the 'in' set in MySQL?

Question:

Here is the problem: I've got a set <strong>A</strong>: (1,2,3,4,5), and a mysql table <strong>B</strong> which looks like:

| id | 1 2 3 6 7

What I want is to select all the elements in A which are not in the table <strong>B</strong>'s id field. Thus, the result should be (4, 5).

I've tried select * from B where id not in (1,2,3,4,5), obviously, the result would be (6, 7).

I just can't get it through how to achieve what I intend to do. Could anyone give me some idea? Thanks a lot!

Answer1:

You can do this using prepared statement. This requires many queries but I think it's a clean solution(IMO).

I am using it in a stored procedure where I get list of comma saperated customer ids and retrieve customer information.

DROP TEMPORARY TABLE IF EXISTS tempSplitValues; CREATE TEMPORARY TABLE tempSplitValues(tempText TEXT); SET @insertStatement = CONCAT('INSERT INTO tempSplitValues VALUES', REPLACE(REPLACE(CONCAT('(', REPLACE("1,2,3,4,5", ',', '), ('), ')'), '(', '("'), ')', '")')); -- Build INSERT statement like this -> INSERT INTO customerIdsTable VALUES("cusId1"),("cusId2") PREPARE stmt FROM @insertStatement; -- parse and prepare insert statement from the above string EXECUTE stmt; -- execute statement DEALLOCATE PREPARE stmt; -- release the statement memory SELECT * FROM tempSplitValues tsv WHERE tsv.tempText NOT IN(SELECT id FROM B);

Answer2:

Use left join instead - it's much more effective.

SELECT A.* FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.ID IS NULL

First - left join records from table B, then filter by B.ID IS NULL - which means table A rows, that doesn't have corresponding records in table B.

Answer3:

There's only two options I can see and neither is particularly elegant (I'd love to see better solutions though).

The first is to use unions to generate the list of numbers:

SELECT * FROM (SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 3 AS id UNION SELECT 4 AS id UNION SELECT 5 AS id) AS a WHERE a.id NOT IN (SELECT id FROM B);

This is pretty hideous however. The second option is to create a temporary table for A and use one of the other answers.

Recommend

  • Modify a Google App Engine entity id?
  • Plot a CSV file where the delimiter is '; ' (semicolon + space)
  • How to escape colon (:) character while executing native SQL queries against an Informix database us
  • WPF Listbox commands
  • How to remove comma or any characters from Python dataframe column name
  • NSMutableArray Access Issue
  • Reassigning an array frees the memory used by it?
  • Creating JS objects in PHP with commas in between
  • Neo4j: Filter nodes based on aggregate function
  • How can I see a list of all files that are different between two Hg repositories?
  • Client side validation mvc dropdown
  • Receiver has no segue with identifier“***”
  • INSERT EXEC Statement cannot be nested [duplicate]
  • Pre-populated SQLite Database not reading properly in Android Studio
  • Get used tables from sql query [duplicate]
  • Command line installation of Code Signing certificates, .p12 files, and mobileprovisions
  • Deploying a CodeRush plugin from the Community Site
  • Magento get URL before current
  • What does “t” refer to in this SQL?
  • Updating Dojo provide
  • How to override value that appears in a dropdown in the rails_admin gem
  • Javascript, Regex - I need to grab each section of a string contained in brackets
  • pillow imaging ImportError
  • Unable to get column index with table.getColumn method using custom table Model
  • Can I display google adwords (AdView) in javafx on android
  • Fetching methods from BroadcastReceiver to update UI
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Can I make an Android app that runs a web view in Chrome 39?
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Web-crawler for facebook in python
  • Delete MySQLi record without showing the id in the URL
  • How do I rollback to a specific git commit
  • Unanticipated behavior
  • Comma separated Values
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Trying to get generic when generic is not available
  • Turn off referential integrity in Derby? is it possible?
  • Add sale price programmatically to product variations
  • Unable to use reactive element in my shiny app
  • How do I use LINQ to get all the Items that have a particular SubItem?