CODEWITHSUNDEEP
sqlperformanceselectoptimizationinformix
JMJ
JMJ

Reputation: 85

Performance required on select query

Consider the following Table_A and Table_B:

Table_A
ID  |A_Field_1
1   |10
2   |11
3   |12

Table_B:

ID  |B_Field_1
1   |500
1   |510
1   |520
2   |550
3   |530

I need to write a query where in to fetch the ID and A_Field_1 for values in Table_B that don't have B_Field_1 values of 520 or 530.

OUTPUT:

ID  |A_FIELD_1
2   |11

My current query is as follows:

SELECT ID,A_Field_1 
from Table_A
where ID not in (select ID from Table_B where B_Field_1 in (520,530))

This works but can someone provide a better performance based query; as in the above query the select ID from Table_B where B_Field_1 in (520,530) inner query is large and corresponds to a growing table.

Upvotes: 2

Views: 92

Answers (2)

Devart
Devart

Reputation: 121932

Try this one -

SELECT
      Id
    , A_Field_1
FROM Table_A a
WHERE EXISTS(
          SELECT 1
          FROM Table_B b
          WHERE B_Field_1 NOT IN (520, 530)
               AND b.Id = a.Id
     )


SELECT
      Id
    , A_Field_1
FROM Table_A a
WHERE NOT EXISTS(
          SELECT 1
          FROM Table_B b
          WHERE B_Field_1 IN (520, 530)
               AND b.Id = a.Id
     )

Upvotes: 2

Amit Singh
Amit Singh

Reputation: 8109

You can try like this also

SELECT
      Id
    , A_Field_1
FROM TABLE_A A
INNER JOIN
(
     SELECT
           Id
         , COUNT(CASE WHEN B_Field_1 IN (520, 530) THEN 1 END) AS ValidId
     FROM Table_B
     GROUP BY Id
) b ON A.Id = b.Id AND b.ValidId < 1

Upvotes: 2

Related Questions