SQL NOT LIKE and LIKE

Question

I'm having a problem with understanding the LIKE and NOT LIKE operators in SQL. This is a query that I've executed:

select serial_number from UNIT U 
group by serial_number
order by serial_number

which yields 2000 results.

When I execute this query, I get 1950 results:

select serial_number from UNIT U 
WHERE op_name LIKE 'Assembly'
group by serial_number
order by serial_number

So when I execute this query, I expect to get 50 results, but instead I get 2000:

select serial_number from UNIT U 
WHERE op_name NOT LIKE 'Assembly'
group by serial_number
order by serial_number

Any explanations? Thanks a bunch.

Answer 1

The group you're doing makes it not really valid to do the sort of count comparison you're attempting. Suppose you have 10 unique serial numbers, and for each of those serial numbers there are two rows (so 20 rows total), one with op_name "Xyz", and another with op_name "Assembly". Your first query would return 10 rows. Your second query would return 10 rows. Your third query would return 10 rows. Because of the group, LIKE "Assembly" and NOT LIKE "Assembly" are not mutually exclusive.