I have the following query
SELECT * FROM TABLA_A
WHERE
CAMPO_1 IN (
SELECT CAMPO_X FROM TABLA_B
)
OR
CAMPO_2 IN (
SELECT CAMPO_X FROM TABLA_B
)
The query works, I hope that the call of the can be optimized in
since they are the same. I have the doubt if there is a performance penalty, since two calls are made to the same query
Edit
As much TABLA_A
as they TABLA_B
can contain duplicate records
Using
EXISTS()
, which checks for at least one record, only generates the subquery 1 time for both columns:Demo en SQLfiddle
And, going a step further in simplification, we can use the
IN
in the opposite sense:Demo en SQLfiddle
INNER JOIN is the simplest option
It must be said that the
or
can be replaced with UNION to improve the performance of your query, you did not specify in your question but this solution can return duplicates if both the value of a.FIELD_1 and a.FIELD_2 are in TABLE_B.@Mariano Thanks for the contribution
show
I agree with Mariano, only that he would omit the field in the EXISTS query, leaving it as follows:
PS: Apologies for not being able to comment on Mariano's answer, but I don't have enough points yet.
Indeed, the JOIN is the best way to optimize the query, only here what we have to clarify is that depending on the problem you want to solve, you can use JOIN, RIGHT JOIN, LEFT JOIN.
As already mentioned earlier, it is necessary to know more about what you are trying to solve.
So I recommend you read a little about Join, and honestly, at work it is very common to find queries that use JOIN, so take care of that clause.
Greetings.