| ==== |
| ---- QUERY |
| # Inner equi-join - executes with hash join. |
| select straight_join atp.id |
| from alltypes atp |
| inner join functional.alltypestiny att on atp.id = att.id |
| where att.int_col = 999 |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Right equi-join - executes with hash join. |
| select straight_join atp.id |
| from alltypes atp |
| right join functional.alltypestiny att on atp.id = att.id |
| where att.int_col = 999 |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Left equi-join - executes with hash join. |
| select straight_join atp.id |
| from alltypes atp |
| left join ( |
| select * from functional.alltypestiny where int_col = 999) att on atp.id = att.id |
| order by atp.id desc |
| limit 5 |
| ---- RESULTS |
| 7299 |
| 7298 |
| 7297 |
| 7296 |
| 7295 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Full outer equi-join - executes with hash join. |
| select straight_join atp.id |
| from alltypes atp |
| full outer join ( |
| select * from functional.alltypestiny where int_col = 999) att on atp.id = att.id |
| order by atp.id desc |
| limit 5 |
| ---- RESULTS |
| 7299 |
| 7298 |
| 7297 |
| 7296 |
| 7295 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Left semi equi-join - executes with hash join. |
| select straight_join atp.id |
| from alltypes atp |
| where id in ( |
| select id from functional.alltypestiny |
| where id = 999) |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Right semi equi-join - executes with hash join. |
| select straight_join atp.id |
| from (select * from functional.alltypestiny att where int_col = 999) att |
| right semi join alltypes atp on atp.id = att.id |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Left NAAJ equi-join - executes with hash join. |
| select straight_join atp.id |
| from alltypes atp |
| where id not in ( |
| select id from functional.alltypestiny |
| where id = 999) |
| order by id desc |
| limit 5 |
| ---- RESULTS |
| 7299 |
| 7298 |
| 7297 |
| 7296 |
| 7295 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Left anti equi-join - executes with hash join. |
| select straight_join atp.id |
| from alltypes atp |
| where not exists ( |
| select id from functional.alltypestiny att |
| where id = 999 and att.id = atp.id) |
| order by id desc |
| limit 5 |
| ---- RESULTS |
| 7299 |
| 7298 |
| 7297 |
| 7296 |
| 7295 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Right anti equi-join - executes with hash join. |
| select straight_join atp.id |
| from (select * from functional.alltypestiny att where int_col = 999) att |
| right anti join alltypes atp on atp.id = att.id |
| order by atp.id desc |
| limit 5 |
| ---- RESULTS |
| 7299 |
| 7298 |
| 7297 |
| 7296 |
| 7295 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Inner non-equi-join - executes with nested loop join. |
| select straight_join atp.id |
| from alltypes atp |
| inner join functional.alltypestiny att on atp.id < att.id |
| where att.int_col = 999 |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Cross join - executes with nested loop join. |
| select straight_join atp.id |
| from alltypes atp, functional.alltypestiny att |
| where att.int_col = 999 |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Left non-equi-join - executes with nested loop join. |
| select straight_join atp.id |
| from alltypes atp |
| left join ( |
| select * from functional.alltypestiny where int_col = 999) att on atp.id < att.id |
| order by atp.id desc |
| limit 5 |
| ---- RESULTS |
| 7299 |
| 7298 |
| 7297 |
| 7296 |
| 7295 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Left semi non-equi-join - executes with nested loop join. |
| select straight_join atp.id |
| from alltypes atp |
| left semi join ( |
| select * from functional.alltypestiny att where int_col = 999) att on atp.id < att.id |
| order by atp.id desc |
| limit 5 |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Left anti non-equi-join - executes with nested loop join. |
| select straight_join atp.id |
| from alltypes atp left anti join ( |
| select * from functional.alltypestiny att |
| where id = 999) att on atp.id < att.id |
| order by id desc |
| limit 5 |
| ---- RESULTS |
| 7299 |
| 7298 |
| 7297 |
| 7296 |
| 7295 |
| ---- TYPES |
| INT |
| ==== |