Find numbers not used in a column

Question

How can I find in a column filled with numbers, the ones that are not used.

Table_A:

'id' int(11) unsigned NOT NULL AUTO_INCREMENT,        
'client_code' int(4) unsigned NOT NULL,    
'client_name' varchar(50) NOT NULL,    
PRIMARY KEY ('id')

INSERT INTO Table_A ('client_code','client_name') VALUES (1,'Bob');    
INSERT INTO Table_A ('client_code','client_name') VALUES (2,'Anna');    
INSERT INTO Table_A ('client_code','client_name') VALUES (5,'Jim');    
INSERT INTO Table_A ('client_code','client_name') VALUES (6,'Tom');    
INSERT INTO Table_A ('client_code','client_name') VALUES (10,'Mattew');

If I SELECT 'client_code' FROM Table_A the output will be 1,2,5,6,10. How can I have the numbers that are not used?

(3,4,7,8,9,11,12...)

Answer 1

To see what's missing, you can compare to a complete list.
There is no row generator in MySQL (like generate_series() in Postgres), but various surrogates are floating around. Like this one in the MySQL forums.

Helper table to provide numbers from 0-9:

CREATE TABLE int10 (i INT);
INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Building on that, generate a complete range of numbers:

SELECT i1.i * 10 + i0.i AS client_code
FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a) -- limit to actual max

Now you can generate the list of missing numbers, exactly as requested:

SELECT GROUP_CONCAT(n.client_code ORDER BY n.client_code) AS missing_codes
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
WHERE  a.client_code IS NULL;

Key elements are the LEFT JOIN and the aggregate function GROUP_CONCAT().
To get full list of codes / names including missing codes, as may be your ultimate goal:

SELECT n.client_code, a.client_name
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
ORDER  BY n.client_code;

-> SQLfiddle demo.

Answer 2

Create a stored procedure as below

DELIMITER $$
CREATE PROCEDURE CheckNotUsedNos(in v1 int)
BEGIN
DROP TEMPORARY TABLE IF EXISTS `incr`;
CREATE TEMPORARY TABLE `incr` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
);
  WHILE v1 > 0 DO
    INSERT incr VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;

Select Id from `incr` 
where Id not in 
(select client_code from `Client`);

END $$
DELIMITER;

Call as you would like to have your parameter. (Just trying with 11 )

call CheckNotUsedNos(11);

And I got below results

ID
3
4
7
8
9
11

SAMPLE FIDDLE

Answer 3

You can have it by the difference between "rownumber" generated for all the rows in the Table_A and client_code:

select * from (
  SELECT @ROWNUMBER:=@ROWNUMBER+1 AS COL FROM Table_A
  JOIN    (SELECT @ROWNUMBER := 0) D) B
where B.COL not in (select client_code from Table_A);

your client_code values have to start from 1 and increment by one.