Let me explain with an example. There is a query:

SELECT T1.num, T2.id FROM T1 INNER JOIN T2 on (T1.prop1 = T2.prop1) and (T1.prop2 = T2.prop2) ........................ and (T1.propN = T2.propN)

if all the fields prop1 ... propN are not NULL, then the query pulls out the necessary data, i.e. takes all the lines from which all the prop fields match.

but if in the fields prop1 ... propN there are NULL values, such strings are truncated, as I understand it. NULL = NULL gives UNKNOWN and they do not join.

How to get around this? What would be lines with properties, for example

  prop1 prop2 prop3

T1: 45 NULL 67
T2: 45 NULL 67

have joined.

Ps.: Thanks in advance!

  • one
    And what LEFT JOIN or there RIGHT JOIN do not work or what? - Barmaley

1 answer 1

For example:

and ( coalesce (T1.prop2, 999) = coalesce (T2.prop2, 999))

In case the column is integer and the value 999 is not in the table.

  • in this case, the lines for one of which are NULL, and for the other, randomly 999, are considered equal, although in fact this is not the case - Sergey041691
  • What did I write? This value should not be. Some fictitious value. I can not think of it, because I do not know what could be in the tables, but I do not think that this is a problem. - msi
  • Thank you, yes, this is an option, it works, only it looks more like crutches, really there is no other way, I meant that if the field is of integer type, then there can be any number from the allowable range from the situation of random equality, only the second indication a parameter in the coalesce () function of some other type, for example, a string, this is not at all beautiful - Sergey041691
  • I do not like it, write so who is stopping: (T1.prop2 = T2.prop2 OR (T1.prop2 IS NULL AND T2.prop2 IS NULL)) - msi
  • do not INNER JOIN, but FULL JOIN. Get what you need. - uilenspiegel