Differences between subqueries and JOIN statements in SQL

Question

LibraryDB is a database system that keeps track of information concerning the books and their circulation in an imaginary library.

The schema for the LibraryDB database is given below:(note that the book_copy relation keeps track of the physical copies of the books in the library collection.)

borrow(transactionID, personID*, borrowdate, duedate, returndate)
author(authorID, firstname, middlename, lastname)
book_copy(bookID, bookdescID*)
book(bookdescID, title, subtitle, edition, voltitle, volnumber, language, place, year, isbn, dewey, subjectID*)
borrow_copy(transactionID*, bookID*)
person(personID, firstname, middlename, lastname, address, city, postcode, phonenumber, emailaddress, studentno, idcardno)
publisher(publisherID, publisherfullname)
written_by(bookdescID*, authorID*, role)
published_by(bookdescID*, publisherID*, role)
subject(subjectID, subjecttype)

I tried to write two types of SQL statements in SQLite to display the title of books that were never borrowed.

One using the LEFT OUTER JOIN clause:

SELECT title
FROM book INNER JOIN book_copy
     ON book.BOOKDESCID = book_copy.BOOKDESCID
        LEFT OUTER JOIN borrow_copy
        ON borrow_copy.BOOKID = book_copy.BOOKID
        WHERE borrow_copy.TRANSACTIONID IS NULL;

which returned 323 rows.

One without using the OUTER JOIN clause:

SELECT title 
FROM book 
WHERE bookdescID IN 
      (SELECT bookdescID 
       FROM book_copy 
       WHERE bookID NOT IN 
             (SELECT bookID 
              FROM borrow_copy));

which only returned 298 rows.

Why these two SQL statements return two different results?

Which one is correct in displaying the title of books that were never borrowed?

Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Alpha75, May 24 '20 at 09:44

score 1 · Accepted Answer · answered May 24 '20 at 06:04

Both queries were used three tables book, book_copy, and borrow_copy. But the first query:

SELECT title
FROM book INNER JOIN book_copy
     ON book.BOOKDESCID = book_copy.BOOKDESCID
        LEFT OUTER JOIN borrow_copy
        ON borrow_copy.BOOKID = book_copy.BOOKID
        WHERE borrow_copy.TRANSACTIONID IS NULL;

selected all books with bookdescID in book_copy, and then selected all records from borrow_copy with bookID in book_copy table. I.e. if two records presented in table borrow_copy for one bookID, that increases the count of rows in the output result.

Second query:

SELECT title 
FROM book 
WHERE bookdescID IN 
      (SELECT bookdescID 
       FROM book_copy 
       WHERE bookID NOT IN 
             (SELECT bookID 
              FROM borrow_copy));

has applied additional filters for the basic table (book).

The second query is correct for searching the books that were never borrowed.

Differences between subqueries and JOIN statements in SQL

1 Answers1