Relation:join

Uses the two relations top of the stack and creates a new relation combining rows and columns based on an expression.

Description

join expression. If both sides have columns of the same name, extend their name with _1 and _2.

join natural: all values of common columns are the same

join left: all values of common columns are the same. Adds empty rows for missing correspoding rows for the left side.

join right all values of common columns are the same. Adds empty rows for missing correspoding for the right side (top relation).

join outer all values of common columns are the same. Adds empty rows for missing correspoding for both sides.

join leftsemi all values of common columns are the same. Retaind only columns of the left side.

join rightsemi all values of common columns are the same. Retains only columns of the left side.

join leftanti not all values of common columns are the same. Retains only columns of the left side.

join rightanti not all values of common columns are the same. Retains only columns of the right side.

join cross combination of all rows and columns of both sides without condition

Parameters

expession: any valid expression

Examples

Using the sample relation films.csv and directors.csv

read "films.csv"
read "directors.csv"
join natural
film director year country gender
A bout de souffle Godard 1960 France M
Tirez sur le pianiste Truffaut 1960 France M
Cléo de 5 à 7 Varda 1962 France F
Jules et Jim Truffaut 1962 France M
Pierrot le fou Godard 1965 France M
Week-End Godard 1967 France M
Die verlorene Ehre der Katharina Blum von Trotta 1975 Germany F
Der starke Ferdinand Kluge 1976 Germany M
Sans toi ni loi Varda 1985 France F
read "directors.csv"
select gender == "F"
write "femaledirectors.csv"
director country gender
Varda France F
von Trotta Germany F
read "films.csv"
read "remaledirectors.csv"
join natural
film director year country gender
Cléo de 5 à 7 Varda 1962 France F
Die verlorene Ehre der Katharina Blum von Trotta 1975 Germany F
Sans toi ni loi Varda 1985 France F
read "films.csv"
read "remaledirectors.csv"
join left
film director year country gender
A bout de souffle Godard 1960
Tirez sur le pianiste Truffaut 1960
Cléo de 5 à 7 Varda 1962 France F
Jules et Jim Truffaut 1962
Pierrot le fou Godard 1965
Week-End Godard 1967
Die verlorene Ehre der Katharina Blum von Trotta 1975 Germany F
Der starke Ferdinand Kluge 1976
Sans toi ni loi Varda 1985 France F
read "films.csv"
read "remaledirectors.csv"
join leftsemi
film director year
Cléo de 5 à 7 Varda 1962
Die verlorene Ehre der Katharina Blum von Trotta 1975
Sans toi ni loi Varda 1985
read "films.csv"
read "remaledirectors.csv"
join leftsemi
film director year
A bout de souffle Godard 1960
Tirez sur le pianiste Truffaut 1960
Jules et Jim Truffaut 1962
Pierrot le fou Godard 1965
Week-End Godard 1967
Der starke Ferdinand Kluge 1976
read "films.csv"
read "directors.csv"
join director_1 == director_2
director film year country gender
Godard A bout de souffle 1960 France M
Truffaut Tirez sur le pianiste 1960 France M
Varda Cléo de 5 à 7 1962 France F
Truffaut Jules et Jim 1962 France M
Godard Pierrot le fou 1965 France M
Godard Week-End 1967 France M
von Trotta Die verlorene Ehre der Katharina Blum 1975 Germany F
Kluge Der starke Ferdinand 1976 Germany M
Varda Sans toi ni loi 1985 France F

Comments

This is the most powerful instruction in Relation. It can also use many ressources, because it may square the data volume.

See also