relational algebra expression for "is null"

Question

What is the relational algebra expression of the query below? I couldn't find the expression for "Is Null".

SELECT reader.name
FROM reader LEFT JOIN book_borrow ON reader.cardid = book_borrow.cardid
WHERE book_borrow.cardid Is Null;

Blackball

What version of RA? Does it have null, and how do its operators treat it? Explain exactly what you & your class/reference mean by the RA "of" or "for" SQL--relations are not tables. – Oppugnant

Answer 1

This task requires a bit of creativity, not a verbatim translation.

What's happening here in this query? First we left join book_borrow on reader. Remember the definition: even if the ON clause matches no rows in the rightmost table, the join will still return a single row such that it contains NULLs in the fields of the right table. Our table looks like this:

reader.name | reader.cardid | book_borrow.cardid | book_borrow.book_id
Alice       | 1             | 1                  | 1
Alice       | 1             | 1                  | 5
Bob         | 2             | 2                  | 5
Charlie     | 3             | NULL               | NULL

We can see that Alice has borrowed two books (of ids 1 and 5), Bob borrowed one (id 5), and Charlie got NULL in his book_borrow fields because he has borrowed none. The query then proceeds to fetch only the rows where book_borrow.cardid is NULL, so the query just says: "get all the people who haven't borrowed any books".

Phrasing the task like this, writing the relational algebra expression is easy:

• let's make a natural join of reader and book_borrow, that will yield us the unwanted rows, names of the people who borrowed any book.
• then just subtract these people from the set of all people, and the result is the set of people who haven't borrowed a book.

Here it is, unleashing my Latex:

The morals of the story: even though, as Erwin points out, there's no null in the purest form of relational algebra (since relational algebra builds on first-order logic), we don't always need it to express lack of something; left joins can be expressed by basic operators; left joins were invented to improve the efficiency of the calculation: you can easily see how taking the left join and selecting nulls is a lot more practical.

Masterly

Left joins cannot possibly be a part of (purest form of) relational algebra, because left joins by definition introduce (possible) nulls in their results. RA can express (the identifying attributes of) things for which a certain other attribute is not known in the database, but that requires two distinct RA relations, and that doesn't map nicely to the single table-with-nulls as per SQL. – Consolidation

Also, it would have been more agreeable if you had pointed out the problem of the duplicate column name 'cardid' in the SQL table. Which is another "feature" that isn't possible in RA. Otherwise, +1 for a "slightly" more helpful answer. – Consolidation

May I clarify for the relational algebra, why is it name, cardid for both projections? Isn't it suppose to be project reader.cardid(READER) - project book_borrow.cardid(READER join BOOK_BORROW)? – Save

@Save in the end we want to output names, so we need to keep the names while we're doing our operations. Projecting so that it keeps only the cardid like you did works perfectly, but loses information. – Cucullate

Answer 2

Null is not a value.

Ergo it is not part of the relational model of data.

Ergo Relational Algebra (if it pretends to be truly relational) cannot and does not have ways for expressing Null-ness.

Consolidation