Is "!=" not the opposite of "="? [closed]

Question

Why if I put = it brings me the correct result and when I put != it brings me the full list instead of the different ones.

SELECT     * 
FROM      library, crime_data 
WHERE     crime_data.id=$oreo 
AND       crime_data.isbn != library.isbn 
AND       crime_data.visibility='0' 
GROUP BY  library.isbn

What am I doing wrong?

Answer 1

You have to think conceptually of what SQL is doing from a set theory viewpoint. When you join two table, the concept is a Cartesian Join – each row of table A is joined with each row of table B. So if you have table of 10 rows and a table of 20 rows and join them, you get a join of 200 rows.

Now when you select using the “=” only rows where the values are equal, usually few, are selected. When you use “!=” it select all the OTHER rows.

---

Edit to answer comment "how should it be".

Select *
From library
Where Not Exists 
    (
        Select 1 
        From  crime_data 
        Where crime_data.isbn = library.isbn 
    )

Answer 2

The way != and = relate is easy to see with a simple example:

SELECT
    1=1,
    1=2,
    1=NULL,
    NULL=NULL,

    1<>1,
    1<>2,
    1<>NULL,
    NULL<>NULL

(I'm using the standard <> operator rather than the MySQL specific one !=, but they're equivalent).

+-----+-----+--------+-----------+------+------+---------+------------+
| 1=1 | 1=2 | 1=NULL | NULL=NULL | 1<>1 | 1<>2 | 1<>NULL | NULL<>NULL |
+-----+-----+--------+-----------+------+------+---------+------------+
|   1 |   0 |   NULL |      NULL |    0 |    1 |    NULL |       NULL |
+-----+-----+--------+-----------+------+------+---------+------------+

So the union of rows where crime_data.isbn = library.isbn and crime_data.isbn != library.isbn does not contain the row total; it excludes all rows where there's at least one NULL operator.