MySQL - optimising selection across two linked tables

Question

I have two MySQL tables, states and trans:

states (200,000 entries) looks like:

id (INT) - also the primary key
energy (DOUBLE)
[other stuff]

trans (14,000,000 entries) looks like:

i (INT) - a foreign key referencing states.id
j (INT) - a foreign key referencing states.id
A (DOUBLE)

I'd like to search for all entries in trans with trans.A > 30. (say), and then return the energy entries from the (unique) states referenced by each matching entry. So I do it with two intermediate tables:

CREATE TABLE ij SELECT i,j FROM trans WHERE A>30.;
CREATE TABLE temp SELECT DISTINCT i FROM ij UNION SELECT DISTINCT j FROM ij;
SELECT energy from states,temp WHERE id=temp.i;

This seems to work, but is there any way to do it without the intermediate tables? When I tried to create the temp table with a single command straight from trans:

CREATE TABLE temp SELECT DISTINCT i FROM trans WHERE A>30. UNION SELECT DISTINCT j FROM trans WHERE A>30.;

it took a longer (presumably because it had to search the large trans table twice. I'm new to MySQL and I can't seem to find an equivalent problem and answer out there on the interwebs. Many thanks, Christian

Answer 1

this should do the trick, I've tested it in SQL Server 2008, so hopefully it works in MySQL too.

select energy from states
where id in
(
    select distinct i from trans where a > 30
    union
    select distinct j from trans where a > 30   
)

Answer 2

OK... with a bit of help from Axarydax and others, I use the (essentially equivalent) command:

SELECT energy FROM states,
(SELECT i FROM trans where A>30. UNION SELECT j i FROM trans WHERE A>30.) temp
WHERE states.id=temp.i;

which works fast enough if I've got an index on i,j and A.