Difference between IN and OR

[madhoriya22]

HI,
I am using this query to get the count from table

 
select count(*) from defect_detail where status = 'RESOLVED' OR 'CLOSED' OR 'VERIFIED'
 

It is retruning count 15. And when I am using this query

 
select count(*) from defect_detail where status in ( 'RESOLVED' , 'CLOSED' , 'VERIFIED')
 

Then it is returnin count as 19. Which one is the correct?

[pbmods]

Heya, madhoriya.

This is more correct than either of the two:

select count(*) from defect_detail where status in ('CLOSED', 'RESOLVED', 'VERIFIED');
 

Note that the items in the IN subclause are sorted. This way, MySQL can use a binary search on the options, which is much faster than going through the list sequentially.

True, MySQL will automatically sort the items for you, but why make it do all that extra work when the values are constants?

Admittedly, when you only have three options, the time savings isn't great, but it's a good habit to get into.

The problem with this statement, which I will go ahead and 'explode' so you can get a better picture of what's going on:

SELECT
        COUNT(*)
    FROM
        `defect_detail`
    WHERE
    (
            `status` = 'RESOLVED'
        OR
            'CLOSED'
        OR
            'VERIFIED'
    )
 

Note that MySQL will only check to see if `status` = 'RESOLVED'. When you use "OR 'CLOSED'", MySQL evaluates 'CLOSED' as true, so in essence your query is doing this:

SELECT
        COUNT(*)
    FROM
        `defect_detail`
    WHERE
    (
            `status` = 'RESOLVED'
        OR
            true
        OR
            true
    )
 

which, of course actually counts ALL the records in the table.

Now, you could do this:

SELECT
        COUNT(*)
    FROM
        `defect_detail`
    WHERE
    (
            `status` = 'RESOLVED'
        OR
            `status` = 'CLOSED'
        OR
            `status` = 'VERIFIED'
    )
 

but it's MUCH faster, especially as your table grows in size, to use IN instead:

SELECT
        COUNT(*)
    FROM
        `defect_detail`
    WHERE
        `status`
            IN
            (
                'CLOSED',
                'RESOLVED',
                'VERIFIED'
            )
 

[madhoriya22]

Heya, madhoriya.

This is more correct than either of the two:

select count(*) from defect_detail where status in ('CLOSED', 'RESOLVED', 'VERIFIED');
 

Note that the items in the IN subclause are sorted. This way, MySQL can use a binary search on the options, which is much faster than going through the list sequentially.

True, MySQL will automatically sort the items for you, but why make it do all that extra work when the values are constants?

Admittedly, when you only have three options, the time savings isn't great, but it's a good habit to get into.

The problem with this statement, which I will go ahead and 'explode' so you can get a better picture of what's going on:

SELECT
COUNT(*)
FROM
`defect_detail`
WHERE
(
`status` = 'RESOLVED'
OR
'CLOSED'
OR
'VERIFIED'
)
 

Note that MySQL will only check to see if `status` = 'RESOLVED'. When you use "OR 'CLOSED'", MySQL evaluates 'CLOSED' as true, so in essence your query is doing this:

SELECT
COUNT(*)
FROM
`defect_detail`
WHERE
(
`status` = 'RESOLVED'
OR
true
OR
true
)
 

which, of course actually counts ALL the records in the table.

Now, you could do this:

SELECT
COUNT(*)
FROM
`defect_detail`
WHERE
(
`status` = 'RESOLVED'
OR
`status` = 'CLOSED'
OR
`status` = 'VERIFIED'
)
 

but it's MUCH faster, especially as your table grows in size, to use IN instead:

SELECT
COUNT(*)
FROM
`defect_detail`
WHERE
`status`
IN
(
'CLOSED',
'RESOLVED',
'VERIFIED'
)
 

Hi,
Thanks buddy. Nice description ! :)

[pbmods]

Heya, Madhoriya.

We aim to please.

Good luck with your project, and if you ever need anything, post back anytime :)