PRESTO Optimising Queries on LEFT JOIN/INNER JOIN

Question

I am running presto query and wondering which will improves the efficiency of the query. For example, I have Table A (Huge amount of rows) and Table B(little amount of rows)

If I want to do a LEFT JOIN, should I

SELECT * FROM TABLE A LEFT JOIN TABLE B ON col_a = col_b
SELECT * FROM TABLE B LEFT JOIN TABLE A ON col_a = col_b

Does it makes any difference? If yes, why? How about inner join? Does it works the same?

It makes - it will give you different results based on data. — Guru Stron, Mar 20 '23 at 12:57
Also just use `EXPLAIN ANALYZE` on your queries. It will give you more relevant info based on your data. — Guru Stron, Mar 20 '23 at 13:22

score 0 · Answer 1 · answered Mar 20 '23 at 13:01

As per the definition, an outer join, be it the left or right, has to perform all the work of an inner join along with the additional work null- extending the results. An outer join is expected to return a greater number of records which further increases its total execution time because of the more significant result set.

hence, Inner join should be faster, but...

If the tables involved in the join operation are too small, say they have less than 10 records and the tables do not possess sufficient indexes to cover the query, in that case, the Left Join is generally faster than Inner Join.

So, in my understanding performance should be similar when they return identical amounts of data, but it might be significantly different when the amounts of data in involved tables are not identical and possess a sufficient index of course. The inner join should be better when table A has much more data than table B.

Sources:

PRESTO Optimising Queries on LEFT JOIN/INNER JOIN

1 Answers1