Show deleted Auto-Increment rows

Question

I created a table with just integers, called Integers, with entries from 1 to 74 to find out which Autoincrement IDs have been deleted from the table ATO, which also has 74 rows

SELECT Integers.ID
  FROM Integers
  LEFT JOIN ATO
    ON Integers.ID = ATO.ID
 WHERE ATO.ID IS NULL

Is there an internal SQL range table I could have used, something like Range[1-74] so I can spare creating this "useless" Integers table? Something like:

SELECT Range [1-74].ID
  FROM Integers
  LEFT JOIN ATO
    ON Range [1-74].ID = ATO.ID
 WHERE ATO.ID IS NULL

I'm testing my design with Microsoft Access because it's quick and dirty and easy to fire SQL queries upon. I will port later to MySQL.

You're looking to create a row generator. They're rarely portable between RDBMS. There's [plenty on Stack Overflow on how to do it in MySQL](http://stackoverflow.com/questions/701444/how-do-i-make-a-row-generator-in-mysql), but I've not used Access enough to know if it's possible. — Ben, Sep 22 '13 at 20:10

score 1 · Answer 1 · edited May 23 '17 at 12:05

Access SQL does not support what you want. If you're highly motivated to eliminate the Integers table, you could substitute a UNION query.

SELECT 1 AS the_number
FROM Dual
UNION ALL
SELECT 2
FROM Dual
UNION ALL
... etc, up to ...
SELECT 74
FROM Dual

Note Dual is a custom table designed to include only one row.

However I'm not sure how many SELECTs you can UNION together. If 74 is possible, it would probably be slow.

With Access SQL, the Integers table is easier.

If you can use something other that SQL, you could create a VBA procedure to examine the ATO table and identify which of your range values are missing from the table.

score 0 · Answer 2 · answered Sep 22 '13 at 19:48

0

you can use the where clause

SELECT Integers.ID from Integers left join ATO on Integers.ID=ATO.ID where ATO.ID is null 
AND Integers.ID BETWEEN 1 AND 74

better yet, use the IN keyword

SELECT ID from Integers WHERE ID BETWEEN 1 AND 74 
AND Integers.ID NOT IN (SELECT ID FROM ATO)

answered Sep 22 '13 at 19:48

Frank Goortani

1,407
17
26

Is there a way to spare the Integers table, since it was only created for the join and has nothing to do with the database at all? – panny Sep 22 '13 at 20:07
1

Maybe you could explain why NOT IN would be better? – Ben Sep 22 '13 at 20:08
Ben, Thanks for adding that. If the joining column is UNIQUE and marked as such, both these queries yield the same plan in SQL Server. My point was the query being less wordy and more readable. This is mentioned in this question : http://stackoverflow.com/questions/1200295/sql-join-vs-in-performance – Frank Goortani Sep 22 '13 at 22:54

Show deleted Auto-Increment rows

2 Answers2