Mysql - How to get results that were not found

Asked

Viewed 117 times

0

I need to verify which Ids were not found in the table. 

SELECT * FROM example_table WHERE id IN (123, 124, 125, 126, 127)

Whereas of (123, 124, 125, 126, 127), on the table I only have (123, 124).

The result I’m waiting for is (125, 126, 127).

I’ve explored the Sub-Querys option but I can’t figure out how to end it.

mysql

  • The idea was to do all this in a Mysql query. But the language is C#.

  • There has to be some way out that allows me to put the "list" of values and receive which of these values is not in the table.

  • All the times I had to do it. I created a table and populated with all the values, so I can select all the items in this table that do not have in the other.

  • For Sqlite I have this solution. SELECT A.X FROM ( WITH RECURSIVE cnt(X) AS (VALUES(123) UNION ALL SELECT X+1 FROM cnt WHERE X < (127)) SELECT X FROM cnt ) A LEFT JOIN ( SELECT CAST( id AS NUMBER ) AS id FROM example_table WHERE id >= 123 and ID <= 127 ) B ON A.X = B.id WHERE B.id IS NULL LIMIT ( 123 - 127 + 1 )

  • 1

    @Reginaldorigo depending on the version of MySql is just like that, the Mysql 8.0 supports CTE

1 answer

0

Remembering that the following solution fits when the need to use is something that compares to what is used in NOT IN, IN. Something with little values. See if this way helps you in something:

SELECT ids.id FROM (SELECT '123' AS id
                    UNION SELECT '124'
                    UNION SELECT '125' 
                    UNION SELECT '126'
                    UNION SELECT '127') ids
LEFT JOIN example_table ex
  ON ex.id = ids.id
WHERE ex.id IS NULL

Exit:

id
125
126
127

I tested it here. Source: https://stackoverflow.com/a/25451956/3658278

  • Let’s say I have a table with numbers, where the smallest is the number 1 and the largest is the 1,000,000. I know that among these numbers there are voids. How do I use this select to show me which numbers are missing?

  • @Yes, I understand. The same rule applies to id IN (123, 124, 125, 126, 127), No? When using IN, NOT IN. In these cases, you can’t use 1 to 1,000,000 either Do you agree with me? The answer is in order to help based on another answer right here in stackoverflow, which no one was negative about. Back to topic here. The reference is for solution within a matrix with few values.

  • Edited response to point out where such an instruction would fit.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.