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;
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:
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.