CODEWITHSUNDEEP
sqldb2
bublitz
bublitz

Reputation: 920

DB2 - Where Subquery

I currently have a problem in db2 with the following tables:

Table_1
A       B        C
1       2        1
2       1        2
3       2        2
4       1        1

Table_2
A
1

I want to select all table_1 records with a B or C greater than the greatest A from table_2. The result should be:

Query
A       B        C
1       2        1
2       1        2
3       2        2

which I currently achieve with this query:

select A, B, C
from Table_1
where B > (select max(A) from Table_2) 
or    C > (select max(A) from Table_2)

Is it possible to only issue one subselect in the where clause to improve performance?

Upvotes: 0

Views: 90

Answers (3)

Turophile
Turophile

Reputation: 3405

I would write it as:

select A, B, C
from Table_1
where MAX(B,C) > (select max(A) from Table_2)

Note: untested as I have no DB2 database handy.

Here it is in an SQLfiddle in MySQL syntax: http://sqlfiddle.com/#!9/2f89c5/3

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Think vice versa: You want all table1 records for which not exists a table2 record with to great an A value:

select *
from table_1
where not exists
(
  select *
  from table_2
  where table_2.a >= table_1.b
    and table_2.a >= table_1.c
);

By the way: Having just one subquery is great for maintainabilty. A Change to it would have to be made in one place only. But as to speed: In your query the subquery is not correlated to the main query, so it will probably be run just once and its result applied to all checks. (It would be stupid, did the DBMS run the same subquery again and again for each record and column in table_1.)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

If that is what you need, then move the subquery to the FROM clause:

select t1.A, t1.B, t1.C
from Table_1 t1 cross join
     (select max(A) as maxA from table_2) t2
where t1.B > t2.maxA or t1.C > t2.maxA;

Upvotes: 0

Related Questions