SELECT A.a, B.b, C.c
FROM A join B on a.id = b.a
join C on b.id = c.b
WHERE A.ax = '...' and B.bx = '...' and C.cx = '....';
SELECT TA.a, TB.b, TC.c
SELECT A.a, A.id
WHERE A.ax = '...'
) as TA
SELECT B.b, B.a, B.id
WHERE B.bx = '...'
) as TB on TA.id = TB.a
SELECT C.c, C.b,
WHERE C.cx = '...'
) as TC on TB.id = TC.b;
Both of your queries depend on several factors like what is indexed and what is not, index strategy, server load, data caching, the way you've written query etc. I assume the columns in
where clause are indexed while answering -
I ran both type of queries and found a fairly similar result. (This could probably be because of how my tables and columns are setup).
Explain plan for Plan A
SELECT STATEMENT FIRST_ROWS Cost: 9 Bytes: 446 Cardinality: 14
Explain plan for Plan B
SELECT STATEMENT FIRST_ROWS Cost: 12 Bytes: 448 Cardinality: 14
Now you see both took same amount of time but Ideally Plan A is what is considered under best practice (speaking from experience, I've seen
Plan A queries being used almost everywhere while
Plan B not so much)
Different queries work differently under different circumstances [and Oracle (or any SQL engine) smartly picks algorithm that would work best for you.]
EDIT - I checked with larger dataset now
Plan A and Plan B both are same
SELECT STATEMENT FIRST_ROWS Cost: 35,413 Bytes: 1,888,512 Cardinality: 59,016