blob: 19f5ea69f8e9152da3c69a6dffa2b8a1400c8d3c [file] [log] [blame]
====
---- 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
====