0

We have a query that needs to compare a value to a list of values in a where clause. The strings are known and discrete. Does IN use the = operator to compare values? Would a b-tree index work for this use case? Example query:

 create table fruit (
   name character varying NOT null
 )

 select * from fruit where name in ('Banana', 'Apple');

Let's assume at the moment we do not want to normalize the data (even though I agree that makes the most sense).

0

An IN statement can be considered a chained OR, e.g. WHERE name IN ('Banana','Apple') is equivalent to WHERE (name = 'Banana' OR name = 'Apple').

The data is normalized once you define Name as your primary key as that would uniquely identify a fruit for the purposes of this example. So if that was your primary key, there would be a B-tree present and the engine would (generally) do two seeks for your query: one for 'Banana' and one for 'Apple'.

If there was a secondary index on Name, the engine would (again, generally) do two seeks on that index and two seeks against the table.

1
  • thanks, that makes a lot of sense! – Jed Schneider Aug 8 '20 at 1:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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