0

I'm trying to have a clausule where what compare a column from table A with a column from table B but the result dont bring nothing. Am I doing something wrong?

My query>

SELECT EMP, COD, VEV, SEQ, TIP, NUM, EMI, VEN, DTR, GRU, PRO, QTD, PVE, TOT, FAT, COM, COO, REF, VAR, hcli.NOM as "VENDEDOR", hemp.enc
 FROM hcov 
    INNER JOIN hcli ON hcov.COD = hcli.COD
    INNER JOIN hemp ON hemp.cod = hcov.emp 
WHERE (hcli.NOM NOT LIKE '%COOPERATIVA%')
AND (DTR >= date '2020-01-01')
AND (COD <> 24545 OR COD <> 10368 OR COD <> 13149 OR COD <> 10448 OR COD <> 11041 OR COD <> 30610 OR COD <> 6834)
AND (GRU <> 266 OR GRU <> 269 OR GRU <> 272)
AND ( (VEV <> 37125) AND (COO <> 987209 OR COO <> 23631 OR COO <> 927500 OR COO <> 22763 OR COO <> 38736 ) )
AND (hemp.enc <> hcov.cod)

AND (hemp.enc <> hcov.cod) -> aparently this line is not working as should

The result is totally blank but both column is different.. so it should bring

enter image description here

Fernando Fefu
  • 157
  • 8
  • 1
    `COD <> 24545 OR COD <> 10368...` will always be true (if COD is not null.) I.e. That code acually means `COD IS NOT NULL`. – jarlh Dec 03 '20 at 19:01
  • 1
    You probably want `COD NOT IN (24545, 10368... )`. – jarlh Dec 03 '20 at 19:02
  • my problem is in this line AND (hemp.enc <> hcov.cod). cause if I remove this line the query works well – Fernando Fefu Dec 03 '20 at 19:03
  • 1
    Same thing there. `AND VEV <> 37125 AND COO NOT IN (987209, ...)` – jarlh Dec 03 '20 at 19:05
  • thanks for these tips.. I fixed my code but still not working... It just work with If I remove the line -> AND (hemp.enc <> hcov.cod) <- .. but I need this comparasion to complete my query.. Am I doing somth wrong? – Fernando Fefu Dec 03 '20 at 19:11
  • are you sure you are not seeing different values for hcov.cod ? you have another column called cov from hcli table , maybe you meant that column? – eshirvana Dec 03 '20 at 19:11
  • I edited the question with an image of the result without the line with problem.. – Fernando Fefu Dec 03 '20 at 19:17
  • this could be related to NULL value your query is not returning any results., https://stackoverflow.com/questions/8036691/sql-not-displaying-null-values-on-a-not-equals-query – coder_b Dec 03 '20 at 21:18

1 Answers1

1

The issue is because you are comparing values where (at least one of them) is NULL.

NULL is not considered a value - it is considered 'unknown'. It could be the value you're checking against, or it may not. Therefore, checking if anything = NULL results in a NULL answer - the answer is also unknown.

Note that even checking if NULL = NULL returns NULL. It may be that the first value is 1 and the second 1 too, therefore it would be true! Or it could be that the second value is 2, therefore it's false. As such, checking if NULL = NULL results in NULL.

You can check if something IS NULL - what this is essentially asking 'whether the value is unknown'. e.g.,

  • if @a is NULL
  • then checking IF @a = NULL results in NULL
  • while checking IF @a IS NULL results in true

In other words, you need to work out how you want the process to work if hemp.enc is NULL and/or hcov.cod is NULL.

For example, you may change the line in the WHERE clause to

AND ((hemp.enc <> hcov.cod) 
     OR (hemp.enc IS NULL AND hcov.cod IS NOT NULL)
     OR (hemp.enc IS NOT NULL AND hcov.cod IS NULL)
    )

Here is a db<>fiddle with some examples of results of checking vs NULLs. Note that only the explicit IS NULL and IS NOT NULL actually return results.

seanb
  • 6,272
  • 2
  • 4
  • 22