- Notifications
You must be signed in to change notification settings - Fork689
Join
Andrey Gershun edited this pageJan 9, 2016 ·9 revisions
Syntax:
SELECT ...FROM table1 joint-typeJOIN table2 (USING|ON)...
Supported join types:
- [INNER JOIN](Inner Join)
- [LEFT [OUTER] JOIN](Left Join)
- [RIGHT [OUTER] JOIN](Right Join)
- [[FULL] OUTER JOIN](Outer Join)
- [ANTI JOIN](Anti Join)
- [SEMI JOIN](Semi Join)
- [CROSS JOIN](Cross Join)
- [NATURAL JOIN](Natural Join)
For example:
alasql('SELECT * FROM Cities JOIN Countries');
alasql('SELECT city.*, country.* FROM city \ JOIN country USING countryid');alasql('SELECT * FROM Cities JOIN Countries USING Country');
alasql('SELECT city.*, country.* FROM city \ JOIN country ON city.countryid = country.countryid');alasql('SELECT * FROM Cities JOIN Countries ON Citites.Country = Countries.Country');
Actually AlaSQL converts internally to the same execution plan, like in this example:
JOIN USING works fine inthis example
vardata={COLORS:[[1,"red"],[2,"yellow"],[3,"orange"]],"FRUITS":[[1,"apple"],[2,"banana"],[3,"orange"]]};data.NEW_FRUITS=alasql('SELECT MATRIX COLORS.[0], COLORS.[1], \ FRUITS.[1] AS [2] FROM ? AS COLORS JOIN ? AS FRUITS USING [0]',[data.COLORS,data.FRUITS]);
With using JOIN ON inthis example:
vardata={COLORS:[[1,"red"],[2,"yellow"],[3,"orange"]],"FRUITS":[[1,"apple"],[2,"banana"],[3,"orange"]]};data.NEW_FRUITS=alasql('SELECT MATRIX COLORS.[0], COLORS.[1], FRUITS.[1] AS [2] \ FROM ? AS COLORS JOIN ? AS FRUITS ON COLORS.[0] = FRUITS.[0]',[data.COLORS,data.FRUITS]);
See the expected results in [Join On Join](Join On Join) page.
© 2014-2026,Andrey Gershun &Mathias Rangel Wulff
Please help improve the documentation by opening a PR on thewiki repo