[
https://issues.apache.org/jira/browse/PHOENIX-167?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maryann Xue updated PHOENIX-167:
--------------------------------
Attachment: 167.patch
1. Change IN/NOT IN/EXISTS/NOT EXISTS into semi/anti joins or left joins:
If the where-clause sub-query is one of those top-node conditions (being the
only condition node or direct descendant of AND nodes), we convert the
sub-query directly into semi-joins, anti-joins or inner-joins, and meanwhile
remove the original condition node from the where clause.
Otherwise, we convert the sub-query into left-joins and change the original
condition node into a null test of a join table field (ONE if matched, NULL if
not matched).
2. Optimize semi-joins:
In circumstances where child-parent join optimization is enough to cover the
semi-join semantics, we won't do joins at all.
> Support semi/anti-joins
> -----------------------
>
> Key: PHOENIX-167
> URL: https://issues.apache.org/jira/browse/PHOENIX-167
> Project: Phoenix
> Issue Type: Sub-task
> Reporter: James Taylor
> Assignee: Maryann Xue
> Labels: enhancement
> Attachments: 167.patch
>
>
> A semi-join between two tables returns rows from the first table where one or
> more matches are found in the second table. The difference between a
> semi-join and a conventional join is that rows in the first table will be
> returned at most once. Even if the second table contains two matches for a
> row in the first table, only one copy of the row will be returned. Semi-joins
> are written using the EXISTS or IN constructs.
> An anti-join is the opposite of a semi-join and is written using the NOT
> EXISTS or NOT IN constructs.
> There's a pretty good write-up [here]
> (http://www.dbspecialists.com/files/presentations/semijoins.html) on
> semi/anti joins.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
