2

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?

3
  • 2
    In SQL Server NULL is not equal to anything even another NULL and we pass character values in 'Values' (single quote). So in SQL Server your query should be Select Distinct id where price is null and category = 'hardware'; When we use DISTINCT or any aggregate function it require sorting. So as you said "SQL command to just fetches any row whatsoever that matches price=null and category = hardware without grouping, sorting, or indexing", you can simply select the columns you require. And if you want to retrieve defined number of recorded, use TOP 10 (num of rows) in SQLServer & LIMIT in MySQL. Jul 3, 2019 at 2:44
  • Thank you for the correction about IS Null (see corrected question).
    – Jimski
    Jul 3, 2019 at 2:57
  • You need a fact that the record matched your criteria exists only. You want to obtain any of id value in that case... maybe you do NOT need in this value, and something like SELECT 1 FROM ... is enough?
    – Akina
    Jul 3, 2019 at 4:39

4 Answers 4

2

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.

1
  • 1
    This will work for Postgres as well (but it will return a true boolean value, not a fake one)
    – user1822
    Jul 4, 2019 at 5:40
4

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.

2

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!

1
  • does limit stop on first match?
    – baxx
    Jan 6, 2021 at 12:44
0

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;
3
  • 1
    What are you going to do with this information? If you're going to introduce that fact it into another query somehow, then consider using where [not] EXISTS (....) rather than 2 steps. Of course this applies to all answers and is pure speculation on my part as to whether that's useful. Jul 3, 2019 at 3:58
  • Yes, it will be fed into another query. I upvoted your comment.
    – Jimski
    Jul 3, 2019 at 3:59
  • 1
    FETCH FIRST n ROWS ONLY is indeed standard compliant, but I don't think all of the DBMS that you tagged support it. Jul 3, 2019 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.