databasenullrelational-algebrarelational

select only those tuples in which a certain attribute is equal to null after outer join in relational algebra


Given table A and table B, using an outer join I create a new table C that has a couple of tuples in which the attribute x is equal to null.

How do I select only those tuples?

Is it correct to use C.x = "null" as a selection predicate?


Solution

  • the attribute x is equal to null

    The standard SQL 3VL way to see whether C.x is NULL is C.x IS NULL. In SQL, = is not "is equal to"; it returns NULLif either operand is NULL. (People say "NULL is not equal to NULL", but that "is equal to" is SQL-=, not "is equal to".) (And they do mean "is not SQL-=", not "SQL-<>", because "NULL SQL-<> NULL" is NULL but they are making a statement understood to be true.

    There is no NULL or 3VL or OUTER JOIN in relational algebra.

    There is no NULL in relational algebra. In SQL the operators treat the value NULL specially, syntactically and semantically, differently than other values, usually by returning NULL when comparing two values when one of them is NULL. So "=" in a WHERE is not equality, it is an operator like equality that acts differently for NULL. So SQL WHERE is not the same operator as algebraic RESTRICT.

    People present systems like relational algebra and call it relational algebra. Whoever gave you the system you are using can tell you how NULL is treated.

    If someone wants to use NULL with the relational algebra to set or query relation variables you have to get straight what operators they mean when they say things like "AND" and "=" (also relational operators like RESTRICT and PROJECT), and whether NULL is a value treated specially by them.