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 you can optimize the call of in since they are equal. I have the doubt if there is a performance penalty, since two calls are made to the same query
Edit
Both TABLA_A as TABLA_B can contain duplicate records
Using EXISTS() , which checks that there is at least one record, only the subquery is generated 1 time for both columns:
SELECT *
FROM TABLA_A
WHERE EXISTS (
SELECT CAMPO_X
FROM TABLA_B
WHERE (TABLA_A.CAMPO_1 = TABLA_B.CAMPO_X)
OR (TABLA_A.CAMPO_2 = TABLA_B.CAMPO_X)
)
And, going one step further in the simplification, we can use the IN in the opposite direction:
SELECT *
FROM TABLA_A
WHERE EXISTS (
SELECT CAMPO_X
FROM TABLA_B
WHERE TABLA_B.CAMPO_X
IN (TABLA_A.CAMPO_1, TABLA_A.CAMPO_2)
)
INNER JOIN It's the simplest option
SELECT * FROM TABLA_A as a
INNER JOIN TABLA_B as b
ON a.CAMPO_1 =b.CAMPO_1 OR a.CAMPO_2 = b.CAMPO_1
It must be said that the or can be replaced with UNION to improve performance of your query, do not specify in your question but this solution can return duplicates if both the value of a.CAMPO_1 and a.CAMPO_2 are in TABLE_B.
@Mariano Thanks for the contribution
SELECT CAMPO_1,CAMPO_2
FROM TABLA_A
WHERE CAMPO_1 IN (
SELECT CAMPO_X
FROM TABLA_B)
UNION
SELECT CAMPO_1, CAMPO_2
FROM TABLA_A
WHERE CAMPO_2 IN (
SELECT CAMPO_X
FROM TABLA_B);
I agree with Mariano, only that I would omit the field in the EXISTS query, staying as follows:
SELECT *
FROM TABLA_A
WHERE EXISTS (
SELECT 1
FROM TABLA_B
WHERE TABLA_B.CAMPO_X
IN (TABLA_A.CAMPO_1, TABLA_A.CAMPO_2)
)
PS: Apologies for not being able to comment on Mariano's response, but I do not have enough points yet.
Indeed the JOIN is the best way to optimize the query, only that 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 you mentioned before, you need to know more about what you want to solve.
So I recommend you read a bit of the Join, and sincerely at work it is very common for you to find queries that use JOIN, so see how you are fond of that clause.
Greetings.