MySQL SELECT query with "<>" operator giving weird result

Question

I have the following query:

SELECT s.id, s.service_measure from service s, user_service_hist ush 
WHERE s.id <> ush.service_id 

My s table looks as follows:

id | service_measure
--------------------
7  | a
8  | b
9  | c

My ush table looks as follws:

id | service_id
--------------------
1  | 7
2  | 8

When I run the above query I expect and want the result to be

id | service_measure
--------------------
9  | c

Instead my result is:

id | service_measure
--------------------
8  | b
9  | c
7  | a
9  | c

It looks like it is running the query 2 times. Once for each row of the ush table. Can anyone tell me why this occurs?

Answer 1

Lets break down your query:

SELECT s.id, s.service_measure 
FROM service s, user_service_hist ush 
WHERE s.id <> ush.service_id 

First the engine does the cross join you've requested by using the , notation in the from clause.

So a,b,c is joined to 1,2 resulting in

1. 7 a
2. 8 b
3. 9 c
4. 7 a
5. 8 b
6. 9 c

A cross join joins every record in a table to every record in another table. these are terribly slow and are seldom are needed. Think about it you have to first take every record in table 1 and pair it with every record from table 2. A join only has to match On like values negating all the work in generating a Cartesian that a cross join does. (I use them Rarely in the work I do; usually when I need to generate test data)

Then the where clause executes which eliminates those where the S.ID and ush.serivce_ID match.

This eliminates lines 1 and 5 above which is why you get only 4 records.

To get your expected results you would need to either to use a not exists or a an outer join instead of a cross join

SELECT s.id, s.service_measure 
FROM service s
LEFT JOIN user_service_hist ush 
 on S.ID = USH.Service_ID
WHERE ush.Service_ID is null

or

SELECT s.id, s.service_measure 
FROM service s
WHERE not exists (Select 1 
                  from user_Service ush 
                  where ush.Service_ID = s.ID)

or (since you seem to be learning about joins )

SELECT s.id, s.service_measure 
FROM user_service_hist ush 
RIGHT JOIN service s
 on S.ID = USH.Service_ID
WHERE ush.Service_ID is null

(notice all we did there was change the order of the joins and the word left to right) right join says include all records from the table on the right and only those matching to the right from the left table. (most people just re-arrange the tables so they are all left joins)

I suppose if the ush table is small an in would work but as the table isn't a set size, increases in records to this table would cause in to degrade in performance over time. The not exists should be the most efficient long term with proper indexes, though the left join offers the ability to get additional data off the ush table if needed; but at a slight hit to performance.

Answer 2

You need a left excluding join! A left excluding join happens when you want all elements from table A where they do not exist on table B. Take a look in the picture!

SELECT 
    s.id, s.service_measure
FROM service s 
    LEFT JOIN user_service_hist ush 
        on ush.service_id = s.id 
WHERE ush.service_id = NULL

Answer 3

Your query performs a cartesian product between the service and the user_service_history tables (that's the from part of the query). The result of this cartesian product is this intermediate table :

s.id | s.service_measure | ush.id | ush.service_id
--------------------------------------------------
7    | a                 | 1      | 7
7    | a                 | 2      | 8
8    | b                 | 1      | 7
8    | b                 | 2      | 8
9    | c                 | 1      | 7
9    | c                 | 2      | 8

This intermediary table is then filtered out by the restriction (where clause) s.id <> ush.service_id, which leaves :

s.id | s.service_measure | ush.id | ush.service_id
--------------------------------------------------
7    | a                 | 2      | 8
8    | b                 | 1      | 7
9    | c                 | 1      | 7
9    | c                 | 2      | 8

Then you keep the s.id and service_measure column and you get your results.

The query you want is (from my head) :

 select s.id, s.service_measure 
 from service s
 where s.id not in (
   select ush.id 
   from user_service_history ush
 )

Answer 4

Try this:

SELECT s.id, s.service_measure 
FROM service s
LEFT JOIN user_service_hist ush ON s.id = ush.service_id
WHERE ush.service_id IS NULL