Error executing query with table join does not display

Question

There is a query that the user has in the bookmarks this product, also considers the product likes and displays all products in the table:

SELECT P.*, 
  COUNT(L.USER_ID) AS LIKES, 
  (B.PRODUCT_ID = P.PRODUCT_ID AND B.USER_ID = B.USER_ID) AS BOOKMARKS 
    FROM PRODUCTS AS P 
      LEFT JOIN LIKES_PRODUCTS AS L ON (L.PRODUCT_ID = P.PRODUCT_ID) 
      LEFT JOIN BOOKMARKS_PRODUCTS AS B ON (B.PRODUCT_ID = P.PRODUCT_ID)
        GROUP BY P.PRODUCT_ID, B.PRODUCT_ID, B.USER_ID ORDER BY P.PRODUCT_ID

But he adds the product to the existing ones in the product and does not display false, but only true, and false displays null for its place:

product_id |  product_name | ... | LIKES | BOOKMARKS |
------------------------------------------------------
     1     |     name 1    | ... |   0   |   true    |
     1     |     name 1    | ... |   0   |   true    |
     2     |     name 2    | ... |   0   |   NULL    |
     3     |     name 3    | ... |   0   |   NULL    |
     4     |     name 4    | ... |   0   |   NULL    |

Why does he add me an extra product 1 and does not display a bookmark column false, but does it null? Where am I wrong help me please.

score 1 · Answer 1 · answered Feb 20 '19 at 05:59

It seems that some of B.PRODUCT_ID, P.PRODUCT_ID, B.USER_ID and B.USER_ID have the SQL NULL value, so the result is NULL.

“NULL” means “unknown” in SQL.

If you check two things for equality, and one of them is unknown, the result is unknown.

If two conditions have to be true, but the result of one condition is unknown, the total result is unknown.

In short, NULL is “contagious”.

Maybe this is what you want:

    B.PRODUCT_ID IS NOT DISTINCT FROM P.PRODUCT_ID
AND B.USER_ID    IS NOT DISTINCT FROM B.USER_ID

IS NOT DISTINCT FROM is like =, but it treats NULL as if it were a normal value.

bignose · Accepted Answer · 2019-02-20T06:31:49.097

With the knowledge that the B.PRODUCT_ID field will be either the same value as the P.PRODUCT_ID, or NULL, you can use the IS NULL and IS NOT NULL comparison operators to ask that question instead.

SELECT […] (B.PRODUCT_ID IS NOT NULL) AS PRODUCT_IS_IN_BOOKMARKS […]

You are dealing with the horror of NULL and three-value logic. NULL makes everything difficult!

does not display a bookmark column false, but does it null?

Short explanation: NULL is not a value; it is the not-a-value placeholder. Asking “is this value equal to don't-have-a-value?” has no meaningful answer, so SQL dictates the response is NULL.

Longer explanation: Your joins are LEFT JOIN, so you are asking for rows even when there is nothing on the right side of that join. When there's no corresponding tuple in the BOOKMARKS table, its columns in the result set are NULL. So your computed field (B.PRODUCT_ID = P.PRODUCT_ID AND B.USER_ID = B.USER_ID) will, when there is no corresponding BOOKMARKS record, be comparing for equality with NULL; and so will get the result NULL.

score 1 · Answer 3 · answered Feb 20 '19 at 06:08

you can use coalesce() for null replace to other unknown id which may never exist in your id return false because null=null checking is not possible

SELECT P.*, 
  COUNT(L.USER_ID) AS LIKES, 
  (coalesce(B.PRODUCT_ID,-99999) = P.PRODUCT_ID AND ) AS BOOKMARKS 
    FROM PRODUCTS AS P 
      LEFT JOIN LIKES_PRODUCTS AS L ON (L.PRODUCT_ID = P.PRODUCT_ID) 
      LEFT JOIN BOOKMARKS_PRODUCTS AS B ON (B.PRODUCT_ID = P.PRODUCT_ID)
        GROUP BY P.PRODUCT_ID, B.PRODUCT_ID, B.USER_ID ORDER BY P.PRODUCT_ID

Error executing query with table join does not display

3 Answers3