My query with a subquery won't return an empty set

Question

I have two tables in MySQL:

Table 1 - WORKORDERS

ID	QUANTITY
1	2
2	1

Table 2 - ITEMSINWORKORDERS

ID	WORKORDER
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 had a left outer join in my statement, so I switched it to inner in hopes of getting an empty set. How can I make it return an empty set whenever there aren't any work orders with missing entries?

Which is intended to find all work orders where not all items have been entered, and the nominal quantity, entered in the WORKORDERS table is greater than the number of records in ITEMSINWORKORDERS corresponding to that work order. I expected to return an empty set. Instead, I get

ID	NOMINAL_QTY	ENTERED_QTY
NULL	NULL	0

Originally, I had a left outer join in my statement, so I switched it to inner in hopes of getting an empty set.

Addendum: I tried solving this myself using NULLIF thus:

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
    )

What I got was frustrating:

ID	NOMINAL_QTY	ENTERED_QTY
NULL	NULL	NULL

Answer 1

I don't know what you expoect as your qiery delivers no rows.

But for count to work you need a GROUP BY

CREATE TABLE WORKORDERS (
  `ID` INTEGER,
  `QUANTITY` INTEGER
);

INSERT INTO WORKORDERS
  (`ID`, `QUANTITY`)
VALUES
  ('1', '2'),
  ('2', '1');

CREATE TABLE ITEMSINWORKORDERS (
  `ID` INTEGER,
  `WORKORDER` INTEGER
);

INSERT INTO ITEMSINWORKORDERS
  (`ID`, `WORKORDER`)
VALUES
  ('1', '1'),
  ('2', '1'),
  ('3', '2');

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 
        ) 
GROUP BY WORKORDERS.ID
      , WORKORDERS.QUANTITY

ID | NOMINAL_QTY | ENTERED_QTY
-: | ----------: | ----------:

SELECT COUNT(ITEMSINWORKORDERS.WORKORDER ) 
          FROM   WORKORDERS INNER JOIN ITEMSINWORKORDERS ON 
                    ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID

| COUNT(ITEMSINWORKORDERS.WORKORDER ) |
| ----------------------------------: |
|                                   3 |

db<>fiddle here