Hello. There is a table categories, it has parent categories, i.e. those in which parent_id = NULL . Why does the following query return 0 rows?

 SELECT * FROM categories WHERE parent_id = NULL

    2 answers 2

    NULL is a “special” value that is similar in meaning to an “undefined value”.

      NULL != NULL

    because one "undefined value" is not necessarily equal to another "undefined value", therefore in the SQL standard there are constructions IS NULL , IS NOT NULL , COALESCE(...) and the like.

    You must use IS NULL / IS NOT NULL :

     SELECT * FROM categories WHERE parent_id IS NULL

    UPDATE: the “intelligible” definition of a NULL value from @Akina :

    I usually suggest using another equivalent: фиг знает . Then the explanation at least becomes logical: "Does the first фиг знает second фиг знает ?" or "Is the value of Х equal to the value of фиг знает ?" (here instead of Х number, line, date or variable is substituted). The correct answer is obvious: and FIG knows.

    • Thank you very much :) - Alexxosipov
    • @Alexxosipov if the answer helped you, mark it with a solution . - Sergey Gornostaev
    • @SergeyGornostaev can only be noted after some time - Alexxosipov
    • 3
      which is similar in meaning to the “indefinite value”. I usually suggest using another equivalent: FIG know . Then the explanation at least becomes logical: "Does the first FIG know the second FIG know ?" or "Is the value of X equal to the value of figs knows ?" (here instead of X the number, line, date or variable is substituted). The correct answer is obvious: a fig knows ... - Akina
    • @Akina is very intelligible: D - Alexxosipov

    In addition, in MS SQL Server, it is possible to control this NULL behavior in predicates using the SET ANSI_NULLS ON/OFF directive, for example:

     SET ANSI_NULLS OFF; IF NULL = NULL PRINT 'TRUE'; ELSE PRINT 'FALSE'; SET ANSI_NULLS ON; IF NULL = NULL PRINT 'TRUE'; ELSE PRINT 'FALSE'; =================== >TRUE >FALSE

    It should be noted that further use of the directive is not recommended :

    In a future version, the SQL Server ANSI_NULLS parameter will always be ON, and applications that explicitly set it to OFF will cause an error. Avoid using this feature in new designs and plan to modify existing applications in which it is applied.

    Although they promise this for more than one year, they have not yet realized their threat :)