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