Stefan Stefan - 26 days ago 8
MySQL Question

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

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

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

SQL Joins

Comments