-1

Foreign Key and Unique Key can have null values right .Suppose FK in Table1 references UK in Table2, can we compare null = null , or such comaprisons will be skipped? Unique key can take 1 NULL value, hence the question.

Adding the example as below

PassportNumber is ForeignKey in PersonTable and Unique Key in Passenger table. If we join the below tables based on PassportNumber will the record come in join or it will be skipped.

Person
Id Name passportNumber
1 John NULL
2 Anil J01234567

Passenger

Id VehicleNumber passportNumber
1 US 7C 0987 NULL
2 UK 8C 0698 J01234567

Vipin
  • 9
  • 1
  • https://stackoverflow.com/questions/7573590/can-a-foreign-key-be-null-and-or-duplicate – Tab Alleman Feb 28 '19 at 13:56
  • Comparring nulls is weird in SQL. You can put an ISNULL(FieldName, '') = ISNULL(FieldName, = ''). Or use the keyword FieldName IS NULL AND FieldNameTable2 IS NULL in where clause – Brad Feb 28 '19 at 13:57
  • 1. Constraints are irrelevant to queries. It doesn't matter what the PKs, UNIQUEs, FKs, etc are. 2. Using NULL is a basic faq that should not be asked again. 3. Your question is not clear. Use enough words, sentences & references to examples to say what you mean. – philipxy Feb 28 '19 at 19:43
  • It's still not clear what exactly you wrote and what exactly you expected (& what exactly you got). Also what did you learn reading about how NULL works & how WHERE works? I even said it was a faq. PS For the future 1. Google 'stackexchange notifications' to learn to use @x to have notification of a comment sent to non-sole non-poster user x. 2. [mcve] – philipxy Mar 01 '19 at 21:58
  • Possible duplicate of [Issues with SQL comparison and null values](https://stackoverflow.com/q/15929269/3404097) – philipxy Mar 01 '19 at 22:35

1 Answers1

1

The NULL values will be skipped. If you want NULLs to match, you need explicit logic:

on (t1.col = t2.col) or (t1.col is null and t2.col is null)

Warning though: the or can kill performance.

Standard SQL has the is not distinct from comparison operator which is "NULL-safe" -- meaning the NULL = NULL. SQL Server does not support such an operator.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786