Search code examples
sqlpostgresqlsql-in

How are IN expressions executed in SQL queries internally?

I have query with an IN clause. 

select * from student where id in (1, 2, 3, 4, 5)

How will the engine parsed and execute this SQL query?
Is it like this, parsed in five different queries or something else?

select * from student where id = 1
select * from student where id = 2
select * from student where id = 3
select * from student where id = 4
select * from student where id = 5
Solution

  • The Postgres query planner translates IN with a list (row) expression:

    select * from student where id in (1, 2, 3, 4, 5);

    exactly like this:

    select * from student where id = 1 OR id = 2 OR id = 3 OR id = 4 OR id = 5;

    You can see this if you run the statement with EXPLAIN.

    Note that there are two basic forms of IN. Details in this related answer on dba.SE: