Instead of writing:
SELECT a.key, a.value
WHERE a.key in
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b ON (a.key = b.key)
So LEFT SEMI JOIN is just allows to implement efficiently IN/EXISTS queries’ semantics in your queries.
Note that right hand side of query cannot be used in WHERE clauses – it should only be used in ON join condition.
Discovered for myself Oracle hierarchical queries power.
Let’s assume you have a table with structure (basically storing graph representation in 1 table, parent is referencing to id in same table):
create table nodes(
id number not null,
type varchar2(10) not null);
Let’s imagine we want to count how many children each parent in this structure has (any node can be both parent and child):
select parent, count(id)
start with id = 1
connect by prior id = parent
group by parent;
Here you can use start with syntax to indicate id which you are asking to start search from.
In this case it will start with specific id = 1, that for each children matched by id = parent it will repeat same operation – looking for the children – therefore full tree will be walked through.
Connect by syntax allows to indicate condition which is used to find matched across whole hierarchy: on the left side of equation (id) you need to indicate parent reference. On the right side (parent) – you need to indicate child reference you are matching with parent.
You can play with SQL provided example here: