Subquery always has results in my query

Question

I have two tables in MySQL:

Table 1 - WORKORDERS

ID	quantity
1	2
2	1

Table 2 - ITEMSINWORKORDERS

ID	Work Order
1	1
2	1
3	2

I have a query:

SELECT WORKORDERS.ID
      , WORKORDERS.QUANTITY AS NOMINAL_QTY
      , COUNT(ITEMSINWORKORDERS.WORKORDER) AS ENTERED_QTY
FROM  WORKORDERS JOIN ITEMSINWORKORDERS ON
         ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID
WHERE WORKORDERS.QUANTITY >
        ( SELECT COUNT(ITEMSINWORKORDERS.WORKORDER )
          FROM   WORKORDERS INNER JOIN ITEMSINWORKORDERS ON
                    ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID
        )

Originally, I used a left outer join in the statement, so I changed it to an inner join, hoping to get an empty set. How do I make it return an empty set when there are no work orders with any missing entries?

The purpose of this query is to find all work orders that do not have all entries entered, and the nominal quantity entered in the WORKORDERS table is greater than the quantity of ITEMSINWORKORDERS records corresponding to the work order. I expected an empty set to be returned. But actually, what I get is

ID	NOMINAL_QTY	ENTERED_QTY
NULL	NULL	0

Originally, I used a left outer join in the statement, so I changed it to an inner join, hoping to get an empty set.

Replenish: I tried using NULLIF to solve this problem as follows:

SELECT WORKORDERS.ID
    , WORKORDERS.QUANTITY AS NOMINAL_QTY
    , NULLIF(COUNT(ITEMSINWORKORDERS.WORKORDER), 0) AS ENTERED_QTY
FROM WORKORDERS JOIN ITEMSINWORKORDERS ON
    ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID
WHERE WORKORDERS.QUANTITY >
    ( SELECT COUNT( ITEMSINWORKORDERS.WORKORDER )
    FROM WORKORDERS INNER JOIN ITEMSINWORKORDERS ON
        ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID
    )

But the results I get are frustrating:

ID	NOMINAL_QTY	ENTERED_QTY
NULL	NULL	NULL

Answer 1

I don't know what you expect since your query doesn't return any rows.

But to make counting work, you need to use GROUP BY.

db<>fiddle here