mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2025-12-28 00:42:56 +00:00
6c066c1a4a
* test: migrate join tests Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: update test results after rebasing main branch Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: unstable query sort results and natural_join test Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: count(*) with joining Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: unstable query sort results and style Signed-off-by: Dennis Zhuang <killme2008@gmail.com> --------- Signed-off-by: Dennis Zhuang <killme2008@gmail.com>
35 lines
1.2 KiB
SQL
35 lines
1.2 KiB
SQL
-- Migrated from DuckDB test: test/sql/join/inner/test_using_join.test
|
|
-- Tests JOIN USING clause
|
|
|
|
CREATE TABLE users_join(user_id INTEGER, username VARCHAR, email VARCHAR, ts TIMESTAMP TIME INDEX);
|
|
|
|
CREATE TABLE orders_join(order_id INTEGER, user_id INTEGER, amount DOUBLE, ts TIMESTAMP TIME INDEX);
|
|
|
|
INSERT INTO users_join VALUES (1, 'alice', 'alice@test.com', 1000), (2, 'bob', 'bob@test.com', 2000);
|
|
|
|
INSERT INTO orders_join VALUES (101, 1, 150.0, 3000), (102, 1, 200.0, 4000), (103, 2, 75.0, 5000);
|
|
|
|
-- JOIN USING (automatically joins on common column)
|
|
SELECT * FROM users_join JOIN orders_join USING (user_id) ORDER BY order_id;
|
|
|
|
-- LEFT JOIN USING
|
|
SELECT * FROM users_join LEFT JOIN orders_join USING (user_id) ORDER BY user_id, order_id NULLS LAST;
|
|
|
|
-- JOIN USING with WHERE
|
|
SELECT * FROM users_join JOIN orders_join USING (user_id) WHERE amount > 100 ORDER BY amount;
|
|
|
|
-- Multiple table JOIN USING
|
|
CREATE TABLE user_profiles(user_id INTEGER, age INTEGER, city VARCHAR, ts TIMESTAMP TIME INDEX);
|
|
INSERT INTO user_profiles VALUES (1, 25, 'NYC', 6000), (2, 30, 'LA', 7000);
|
|
|
|
SELECT * FROM users_join
|
|
JOIN orders_join USING (user_id)
|
|
JOIN user_profiles USING (user_id)
|
|
ORDER BY order_id;
|
|
|
|
DROP TABLE user_profiles;
|
|
|
|
DROP TABLE orders_join;
|
|
|
|
DROP TABLE users_join;
|