Sql-server – SQL – How to return first encountered row that matches specific criteria

MySQLpostgresqlsql server

I have a table that has the following format:

id | price | category
---------------------
1   22    hardware
2   11    software
3   null  hardware
4   null  hardware
5   55    software
6   null  hardware

I want to fetch any row whatsoever that has price = null and category = hardware. I only want to know if a row with a null price exists. So whether the query fetches row ID 3 or 4 or 6 doesn't matter.

I'm currently using 

Select Distinct id where price is null and category = hardware

but on a very large table with many null values the Distinct will require internal sorting by the database which will take extra time.

Other options that come to mind are 

Select max(id) where price is null and category = hardware

But all those options require some internal sorting, grouping or indexing.

Is there a simpler SQL command to just fetches any row whatsoever that matches price=null and category = hardware without grouping, sorting, or indexing?

Best Answer

I only want to know if a row with a null price exists.

MySQL:

SELECT
    EXISTS ( SELECT 1 FROM tbl
                  WHERE price IS NULL
                    AND category = 'hardware' );

will return true or false (1 or 0).

Beneficial:

INDEX(price, category) -- in either order

With that index it will be very fast, regardless of table size, since it only needs to check one row in the BTree.

If you need id, then Rajish has the optimal answer, but it needs the index I recommend.

Related Question