How to Find Missing number in Sequence using mysql? SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
   FROM wp_blogs AS a, wp_blogs AS b
   WHERE a.blog_id &lt; b.blog_id
   GROUP BY a.blog_id
   HAVING start &lt; MIN(b.blog_id);


It's working for me thank you for this post and i need brief explanation about this query
Without much experience in writing SQL queries, it's hard to understand by simply staring it down. A good approach is to "rebuild" it to understand what each step does.

Start with some sample data:

CREATE TABLE wp_blogs
    (`blog_id` int)
;

INSERT INTO wp_blogs
    (`blog_id`)
VALUES
    (1),
    (2),
    (3),
    (6),
    (7),
    (9)
;


Now we rebuilt it step by step. Start with the JOIN. (I don't like joining in the where clause, so I've rewritten it, but it works the same):

SELECT
a.blog_id as aa,
b.blog_id  as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id &lt; b.blog_id
ORDER BY aa, bb /*added the ORDER BY for readability in the output*/


This has the following output:

| aa | bb |
|----|----|
|  1 |  2 |
|  1 |  3 |
|  1 |  6 |
|  1 |  7 |
|  1 |  9 |
|  2 |  3 |
|  2 |  6 |
|  2 |  7 |
|  2 |  9 |
|  3 |  6 |
|  3 |  7 |
|  3 |  9 |
|  6 |  7 |
|  6 |  9 |
|  7 |  9 |


As you can see the table is joined to itself, and on the right side only the rows are displayed that are greater than the left side.

Now we continue with the GROUP BY to collapse the groups on the left side. Whatever column you have in the SELECT clause, but not in the GROUP BY clause, you have to apply an aggregate function on it. Otherwise it's not clear which row for each group should be displayed. You have to tell MySQL, that you want the smallest number in the group. This can be done like this:

SELECT
a.blog_id as aa,
MIN(b.blog_id) as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id &lt; b.blog_id
GROUP BY aa
ORDER BY aa, bb


Result is:

| aa | bb |
|----|----|
|  1 |  2 |
|  2 |  3 |
|  3 |  6 |
|  6 |  7 |
|  7 |  9 |


What we are interested in now, are the rows where the number on the right is not the number that is followed immediately after the number on the left. We filter on this with the HAVING clause, not with the WHERE clause, because we're dealing with groups here. In your query this is done with HAVING start &lt; MIN(b.blog_id). start refers to the alias set in the SELECT clause for a.blog_id+1.
In other words, we add 1 to the value in the left column. If this is equal to the value on the right side, don't display the row.

So the result of this is:

SELECT
a.blog_id + 1 as aa,
MIN(b.blog_id) as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id &lt; b.blog_id
GROUP BY aa
HAVING aa &lt; MIN(b.blog_id)
ORDER BY aa, bb

| aa | bb |
|----|----|
|  4 |  6 |
|  8 |  9 |


bb is the number that is found in the table. That's why in your statement in the SELECT clause again 1 is subtracted from MIN(b.blog_id). The final query is:

SELECT
a.blog_id + 1 as aa,
MIN(b.blog_id) - 1 as bb
FROM
wp_blogs a
JOIN wp_blogs b ON a.blog_id &lt; b.blog_id
GROUP BY aa
HAVING aa &lt; MIN(b.blog_id)
ORDER BY aa, bb

| aa | bb |
|----|----|
|  4 |  5 |
|  8 |  8 |


Hope it's clear now :)
Play around with it in this sqlfiddle if you like