Query of remaining bibliography (1)

There are the following basic tables:

You must complete the following query:

(1) list the remaining titles and quantity

(2) list all titles and the number of remaining titles

Solution:

(1 ):

Select title, count (title) as available
From book
Where id not in
(Select bookid
From borrow)
Group by title;

Query results:

(2 ):

Select title, count (ID)-count (userid) as remain
From book left join borrow on book. ID = borrow. bookid
Group by title;

Query results:

It can be seen that the difference from (1) is that there are no remaining titles listed.

Note:

1, (2) the left Outer Join is used. The effect is to keep the left side of the join without losing entries, such:

Select *
From book left join borrow on book. ID = borrow. bookid;
Query results:

2. Count (userid) does not count null values.