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

Question

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?

Answer 1

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.

Answer 2

Answering for SQL Server. I don’t know that there will be a single answer that will work on all three platforms you’ve tagged. If that was unintentional, please only tag the engine you’re using. If you want an answer per platform, might be better to ask separate questions.

You can leave which id is returned up to SQL Server by using an arbitrary TOP (1) (no order by):

SELECT TOP (1) id 
  FROM dbo.table
  WHERE price IS NULL
    AND category = 'hardware';

I would comment the code to say “don’t care which one and I know this is arbitrary” so a future reader doesn’t think you made any assumptions about deterministic order.

Answer 3

1st: like I commented, we can't compare NULL value with any value or another NULL.

2nd: If table has index, database engine will use it for better performance of the queries we execute on the table.

So as you required to validate existence of record without bothering database engine to do sorting or grouping.

You can use the query suggested by Aaron Bertrand if you are using SQL Server.

If you are using MySQL or PostgreSQL,following query will help you.

SELECT
        id 
FROM    dbo.table
WHERE   price IS NULL
        AND category = 'hardware'
LIMIT 1;

Thanks!

Answer 4

After some digging I found this nice and intuitive answer compliant with SQL standard:

SELECT id 
FROM   my_table 
WHERE  price IS NULL
AND    category = 'hardware'
FETCH FIRST 1 ROW ONLY;