How to get the row count from a table related to other table? I am new to mysql and database concepts and I will try to explain my question through these two minimal tables, here is the scenario for which I need to write a query,

Table : Fruits
| FruitId | FruitName |
| -------- | -------------- |
| 1        | Mango          |
| 2        | Apple          | 
| 3        | Papaya        |

Table : FruitSubClasses
| FruitSubClassID | FruitSubClass   | FruitID|
| ---------- -----| --------------- |-----------|
| 100             | Alphonso        |1|
| 101             | Irwin           |1|
| 200             | Fuji            |2|
| 201             | Golden Delicious|2|

I want to get data of all such Fruits from Table 1 such that there are no rows for these fruits(no subclasses) in Table 2. From what I searched I think I will need to use Joins and Count but I don't know how to use them in this situation.
I can describe it like &quot;For each FruitID in Fruits, Fetch the FruitID and FruitName from Fruits such that this FruitID is not present in FruitSubClasses&quot; but could not make a working query for this.
You would need an OUTER JOIN to solve this one, particularly a LEFT OUTER JOIN (also known as LEFT JOIN) like so:
SELECT F.FruitID, F.FruitName
FROM Fruits F
LEFT JOIN FruitSubClasses FSC
    ON F.FruitID = FSC.FruitID
WHERE FSC.FruitID IS NULL

A LEFT JOIN returns all rows from the table on the left-hand side of the JOIN clause regardless of the match in the predicate of the ON clause. For rows where there are no matches on the right-hand side, NULL values are returned for the columns of the right-hand table. Knowing this we can then filter out anything that matches in the JOIN with the WHERE clause above, and only get the non-matching Fruits (that don't exist in FruitSubClasses).
And if you wanted just the row counts in a single scalar value, you can use the same query above with the COUNT(*) function like so:
SELECT COUNT(*)
FROM Fruits F
LEFT JOIN FruitSubClasses FSC
    ON F.FruitID = FSC.FruitID
WHERE FSC.FruitID IS NULL