Check value if exists in column

Question

I'd like to know how to maximize speed when querying for the presence of a varchar value in a column in a specific table. I don't need to know where it is, or how many occurrences there are, I just want a true/false. Of course, the column has an index.

Now, I have this:

SELECT exists (SELECT 1 FROM table WHERE column = <value> LIMIT 1);

For check if a column name exists, see stackoverflow.com/a/10636439/287948 — Peter Krauss
– Peter Krauss, Commented May 17, 2012 at 13:20

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-11-16 10:39:15Z

33

EXISTS should normally return as soon as the subquery finds one row that satisfies its WHERE clause. So I think your query is as fast as you can make it.

I was a little surprised that LIMIT 1 seems to always speed up the query very slightly. I didn't expect that. You can see the effect with EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT exists (SELECT 1 FROM table WHERE column = <value> LIMIT 1);

answered Nov 16, 2011 at 10:39

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Perlos Over a year ago

Can you tell me how query is commonly used for this (check if exist with max speed)

blindguy · Accepted Answer · 2023-03-31 22:25:35Z

2

I have had recent success with the following in some cases:

SELECT count(1) > 0
FROM table
WHERE column = <value>

edited Mar 31, 2023 at 22:25

answered Mar 11, 2020 at 16:57

blindguy

1,04912 silver badges17 bronze badges

4 Comments

Safwan Ljd Over a year ago

what do you mean "in some cases"?

blindguy Over a year ago

@SafwanLjd Surprisingly using 'count(1) > 0' is faster in some cases and exists is faster in others. In most cases 'count(1) > 0' leads to cleaner looking code.

user3503711 Over a year ago

I guess the correct query will be: SELECT count(1) > 0 FROM table WHERE column = <value>;

Bala Over a year ago

In my case exist() takse 3ms to execute the query but count() takes whooping 20ms so I would suggest to go with exist(). My conf is Postgresql with 88862 rows of table.

Collectives™ on Stack Overflow

Check value if exists in column

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related