Details
-
Improvement
-
Status: Open
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
-
None
-
ghx-label-13
Description
We are facing a scenario where two tables are outer joined but only fields from the outer side table are used and the join key of the inner side table is guaranteed to be unique. Take the following simplified query as an example, where s.id is guaranteed to be unique:
-- drop the test tables if exists: drop table if exists t; drop table if exists s; -- create test tables: create table t (s_id bigint, value bigint); create table s(id bigint, value bigint, primary key(id)); -- the test SQL: select t.* from t left join s on t.s_id = s.id;
the above query can be optimized to the following if we can utilize the primary key information:
select t.* from t;