How to formulate an SQL-In-Query more efficiently?-CodePudding

I have the following SQL-query:

select x
from table1 t1 join table2 t2 on t1.x = t2.x
where t1.x in ( SUBQUERY )

The result of the subquery is fixed and independent of the rest of the query.

If I write the code for the subquery in there, it takes ages, even though the subquery evaluates rather fast. However, if I paste the result values of the subquery in there manually, the whole query evaluates fast again. I assume this is because using the code instead of pasting it manually results in unnecessary multiple evaluations of the subquery.

How can I avoid those?

Thanks in advance

CodePudding user response：

On many Databases, if the result of the subquery which uses in statement is large, then your performance will be bad. And not in command gets very very bad performance. I recommended to you use joining tables. For Example:

-- not recommended
select * from test_table a1 
where id in (select id from test)

-- recommended
select * from test_table a1 
inner join test a2 on a1.id = a2.id

And instead of NOT IN:

-- not recommended
select * from test_table a1 
where id not in (select id from test)


-- recommended
select * from test_table a1 
left join test a2 on a1.id = a2.id 
where a2.id is null;