Using DatabaseSync in Node, I can do the following:

const stmt = conn.prepare(`select name from people where id = ?`);
let res = stmt.get(100)

However, I want to select a number of records at once using the IN operator, so I tried the following, thinking that's quite expected, but it does not work:

const stmt = conn.prepare(`select name from people where id in (?)`);
res = stmt.get([100,200,300])

Also:

res = stmt2.get(1,2,3)

How do I actually use the WHERE x IN (y1, y2, ... yn) in this case with parameters?

Using DatabaseSync in Node, I can do the following:

const stmt = conn.prepare(`select name from people where id = ?`);
let res = stmt.get(100)

However, I want to select a number of records at once using the IN operator, so I tried the following, thinking that's quite expected, but it does not work:

const stmt = conn.prepare(`select name from people where id in (?)`);
res = stmt.get([100,200,300])

Also:

res = stmt2.get(1,2,3)

How do I actually use the WHERE x IN (y1, y2, ... yn) in this case with parameters?

• node.js
• database
• sqlite
• select

2 Answers 2

Instead of a single parameter, try constructing a list of parameters which corresponds to number of binding values.

For example:

const values = [100,200,300];

// 3 values, so need 3 params: ?,?,?
const params = values.map(_=>'?').join(',');

const stmt = conn.prepare(`select name from people where id in (${params})`);
res = stmt.get(values);

Here is another way to do it that may or may not work better in your situation. It uses and in memory table and is often faster because joins are typically faster than the in statement. It will be much faster if the table is already in the database.

' create table to join to
WITH itemofinterest(item) AS ( 
   SELECT 100
   UNION ALL
   SELECT 200
   UNION ALL
   SELECT 300
)
' join to table to get items of interest
SELECT name 
FROM people 
JOIN itemsofinterest on i.item = id