How to use actual row count (COUNT(*)) in WHERE clause without writing the same query as subquery?

In MySQL, you can only do what you tried:

SELECT id, fruit, pip FROM   plant WHERE  (        SELECT COUNT(*)         FROM   plant       ) = 2;

or this variation:

SELECT id, fruit, pip FROM   plant   JOIN      (        SELECT COUNT(*) AS cnt         FROM   plant       ) AS c    ON c.cnt = 2;

Whether the 1st or the 2nd is more efficient, depends on the version of MySQL (and the optimizer). I would bet on the 2nd one, on most versions.

In other DBMSs, that have window functions, you can also do the first query that @Andomar suggests.

Here is a suggestion to avoid the bottleneck of calculating the derived table twice, once to get the rows and once more to get the count. If the derived table is expensive to be calculated, and its rows are thousands or millions, calculating them twice only to throw them away, is a problem, indeed. This may improve efficiency as it will limit the intermediately (twice) calculated rows to 3:

SELECT  p.*FROM    ( SELECT id, fruit, pip       FROM   plant       LIMIT 3    ) AS p  JOIN    ( SELECT COUNT(*) AS cnt      FROM           ( SELECT 1           FROM   plant           LIMIT 3        ) AS tmp    ) AS c    ON c.cnt = 2 ;

After re-reading your question, you're trying to return rows only if there are 2 rows in the entire table. In that case I think your own example query is already the best.

On another DBMS, you could use a Windowing function:

select  *from    (        select  *        ,       count(*) over () as cnt        from    plant        ) as SubQueryAliaswhere   cnt = 2

But the over clause is not supported on MySQL.

old wrong anser below

The where clause works before grouping. It works on single rows, not groups of rows, so you can't use aggregates like count or max in the where clause.

To set filters that work on groups of rows, use the having clause. It works after grouping and can be used to filter with aggregates:

SELECT id, fruit, pip FROM   plant GROUP BY       id, fruit, pip HAVING COUNT(*) = 2;

The other answers do not fulfill the original question which was to filter the results "without using a subquery".

You can actually do this by using a variable in 2 consecutive MySQL statements:

SET @count=0;SELECT * FROM(    SELECT id, fruit, pip, @count:=@count+1 AS count    FROM   plant     WHERE) tmpWHERE @count = 2;