LEFT JOIN with NULL, NOT EQUAL both of them showing unexpected result

77
December 16, 2018, at 05:50 AM

Get shocked ! What is wrong with following code ? Neither LEFT JOIN with NULL is selecting Null rows, Nor MYSQL != operator is showing only non-matched rows

Two table

Table book - lists of books

id  |   ttl
===========
1   |   Science
2   |   Math
3   |   English

Table block_book - lists of book assigned to each block

id  |   block|  book
=====================
1   |   1   |   1
2   |   1   |   2

Now, requirement is to show the rest of the books that are not assigned to blocks. So what I do

SELECT
    book.id AS id, book.ttl AS book 
FROM
    block_book
JOIN
    book ON book.id != block_book.book
GROUP BY book.id

With the != operator it should be following result, shouldn't it ? because only the subject English is not in table block_book

Expected Result

id  |   book
===========
3   |   English

But all books are selected. Current Result

id  |   book
===========
1   |   Science
2   |   Math
3   |   English

I tried with Left JOIN with Null

SELECT
    book.id AS id, book.ttl AS book 
FROM
    block_book
LEFT JOIN
    book ON book.id = block_book.book
WHERE block_book.book IS NULL

but it returns nothing

SQL Fiddle

Answer 1

You should use right join.

So this should work:

SELECT
book.id AS id, book.ttl AS book 
FROM
block_book
RIGHT JOIN
book ON book.id = block_book.book
WHERE block_book.book IS NULL

Or if you want to use left join, you should write the query like this:

SELECT
book.id AS id, book.ttl AS book 
FROM
book
LEFT JOIN
block_book ON book.id = block_book.book
WHERE block_book.book IS NULL
Answer 2

Now, requirement is to show the rest of the books that are not assigned to blocks.

The most direct way to approach this is to use NOT EXISTS:

select b.*
from book b
where not exists (select 1
                  from block_book bb
                  where bb.book = b.id
                 );
READ ALSO
How to get amount of data transfered between PHP and DB server from inside PHP code?

How to get amount of data transfered between PHP and DB server from inside PHP code?

I want to analyze the amount of traffic certain scripts use when communicating with DBI use mysqli_connect, then I do mysqli_query and then usually mysqli_fetch_assoc or mysqli_fetch_array

89
A strange SQL syntax

A strange SQL syntax

I can't understand the SQL syntax here: CREATE

81
Is it possible to use vector drawables in Nativescript as Image source?

Is it possible to use vector drawables in Nativescript as Image source?

I'm creating Nativesctript Angular app for AndroidI used vector drawables (downloaded from here) in iconSource of tabItem and they work ok

115