I need some help with a problem in relational algebra:
Let's say we have table A and table B and, using an outer join, I create a new table and that table has a couple of tuples in which the attribute x
is equal to null
.
What should I do to select only these tuples?
Is it correct to use, as a selection predicate
TabC.x = "null"
Are there other methods?
the attribute
x
is equal tonull
The standard SQL 3VL way to see whether TabC.x
is NULL
is TabC.x IS NULL
. In SQL, =
is not "is equal to"; it returns NULL
if 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.