MySQL：从没有关系的多个表中进行选择

Question

First post, be gentle! My SQL knowledge is average, at best.

I have 2 tables with no direct relationship to each other.

• TableA ("CustomerWishList") with columns "bookID", other-book-data, etc etc etc
• TableB ("CustomerOwned") with columns - "bookID", other-book-data, etc etc etc

A book could be in one, both, or none of those tables.

I need to create SQL statement that counts the number of "bookID='data'" occurrences in either TableA or TableB.

So far using PHP I'm searching TableA "bookID" column first, and then if nothing found, searching TableB "bookID" column next ... it works, but is inefficient, and am convinced there must be a better way.

JOIN statements don't seem to apply here? - I could be wrong.

---

UPDATED

Psuedo PHP code I'm currently using:

$total=SELECT COUNT(*) FROM CustomerWishList WHERE BOOKID=1234

if ($total==0) {
    $total2=SELECT COUNT(*) FROM CustomerOwned WHERE BOOKID=1234
}  
return $total2;

Answer 1

Below query would give you count of book ids which are only in table A:

SELECT count(1) as `count`
FROM tableA
WHERE NOT EXISTS
 (SELECT bookID FROM tableB WHERE bookID = tableA.bookID);

Similarly, following query would give you count of book ids which are only in table B:

SELECT count(1) as `count`
FROM tableB
WHERE NOT EXISTS
 (SELECT bookID FROM tableA WHERE bookID = tableB.bookID);

Now, if you want to add these two then you can use the following query:

SELECT `a.count` + `b.count`
FROM ( 
 SELECT count(1) as `count`
 FROM tableA
 WHERE NOT EXISTS
  (SELECT bookID FROM tableB WHERE bookID = tableA.bookID)
 ) a,
(
 SELECT count(1) as `count`
 FROM tableB
 WHERE NOT EXISTS
  (SELECT bookID FROM tableA WHERE bookID = tableB.bookID);
) b;