CODEWITHSUNDEEP
sqlpostgresqlsql-in
Vijay Thayalan
Vijay Thayalan

Reputation: 83

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

Upvotes: 2

Views: 84

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

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:

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269693

No, it will not do that although the five queries and the one query will return the same result (assuming no modifications to the table while running five queries). The five queries would require scanning the student table five times, once for each query. The "scan" might actually use an index an be really fast.

The five queries also need to be compiled and executed, which add additional overhead.

With the in, you are executing one query. Without an index, Postgres will look at each row to see if it matches. If so, it goes in the result set. With an index, it will just look up the appropriate rows in the index.

Upvotes: 3

Related Questions