[PHOENIX-167] Support semi/anti-joins

• Type: Sub-task
• Status: Closed
• Resolution: Fixed
• Fix Version/s: 4.2.0, 3.2.0
• Labels: enhancement
• old issue number: 36
• Assignee: Wei Xue
• Reporter: James R. Taylor
• Created: 10/Mar/14 23:46
• Updated: 21/Nov/15 02:17
• Resolved: 07/Oct/14 14:27

Description

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.