mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-10 23:32:55 +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>
134 lines
4.3 KiB
Plaintext
134 lines
4.3 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/join/full_outer/ complex tests
|
|
-- Tests complex outer join scenarios
|
|
CREATE TABLE employees(emp_id INTEGER, "name" VARCHAR, dept_id INTEGER, salary INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE departments(dept_id INTEGER, dept_name VARCHAR, manager_id INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE projects(proj_id INTEGER, proj_name VARCHAR, dept_id INTEGER, budget INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO employees VALUES
|
|
(1, 'Alice', 10, 75000, 1000), (2, 'Bob', 20, 65000, 2000),
|
|
(3, 'Charlie', 10, 80000, 3000), (4, 'Diana', 30, 70000, 4000), (5, 'Eve', NULL, 60000, 5000);
|
|
|
|
Affected Rows: 5
|
|
|
|
INSERT INTO departments VALUES
|
|
(10, 'Engineering', 1, 1000), (20, 'Marketing', 2, 2000), (40, 'HR', NULL, 3000);
|
|
|
|
Affected Rows: 3
|
|
|
|
INSERT INTO projects VALUES
|
|
(101, 'ProjectA', 10, 100000, 1000), (102, 'ProjectB', 20, 150000, 2000),
|
|
(103, 'ProjectC', 30, 75000, 3000), (104, 'ProjectD', 50, 200000, 4000);
|
|
|
|
Affected Rows: 4
|
|
|
|
-- Full outer join with multiple conditions
|
|
SELECT
|
|
e.emp_id, e."name", d.dept_id, d.dept_name
|
|
FROM employees e
|
|
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
|
|
ORDER BY e.emp_id, d.dept_id;
|
|
|
|
+--------+---------+---------+-------------+
|
|
| emp_id | name | dept_id | dept_name |
|
|
+--------+---------+---------+-------------+
|
|
| 1 | Alice | 10 | Engineering |
|
|
| 2 | Bob | 20 | Marketing |
|
|
| 3 | Charlie | 10 | Engineering |
|
|
| 4 | Diana | | |
|
|
| 5 | Eve | | |
|
|
| | | 40 | HR |
|
|
+--------+---------+---------+-------------+
|
|
|
|
-- Left outer join with IS NULL filter
|
|
SELECT
|
|
e.emp_id, e."name", e.dept_id, d.dept_name
|
|
FROM employees e
|
|
LEFT OUTER JOIN departments d ON e.dept_id = d.dept_id
|
|
WHERE d.dept_id IS NULL
|
|
ORDER BY e.emp_id;
|
|
|
|
+--------+-------+---------+-----------+
|
|
| emp_id | name | dept_id | dept_name |
|
|
+--------+-------+---------+-----------+
|
|
| 4 | Diana | 30 | |
|
|
| 5 | Eve | | |
|
|
+--------+-------+---------+-----------+
|
|
|
|
-- Right outer join
|
|
SELECT
|
|
e.emp_id, e."name", d.dept_id, d.dept_name
|
|
FROM employees e
|
|
RIGHT OUTER JOIN departments d ON e.dept_id = d.dept_id
|
|
ORDER BY d.dept_id, e.emp_id;
|
|
|
|
+--------+---------+---------+-------------+
|
|
| emp_id | name | dept_id | dept_name |
|
|
+--------+---------+---------+-------------+
|
|
| 1 | Alice | 10 | Engineering |
|
|
| 3 | Charlie | 10 | Engineering |
|
|
| 2 | Bob | 20 | Marketing |
|
|
| | | 40 | HR |
|
|
+--------+---------+---------+-------------+
|
|
|
|
-- Triple outer join
|
|
SELECT
|
|
e."name", d.dept_name, p.proj_name, p.budget
|
|
FROM employees e
|
|
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
|
|
FULL OUTER JOIN projects p ON d.dept_id = p.dept_id
|
|
ORDER BY e.emp_id, p.proj_id;
|
|
|
|
+---------+-------------+-----------+--------+
|
|
| name | dept_name | proj_name | budget |
|
|
+---------+-------------+-----------+--------+
|
|
| Alice | Engineering | ProjectA | 100000 |
|
|
| Bob | Marketing | ProjectB | 150000 |
|
|
| Charlie | Engineering | ProjectA | 100000 |
|
|
| Diana | | | |
|
|
| Eve | | | |
|
|
| | | ProjectC | 75000 |
|
|
| | | ProjectD | 200000 |
|
|
| | HR | | |
|
|
+---------+-------------+-----------+--------+
|
|
|
|
-- Outer join with aggregation
|
|
SELECT
|
|
d.dept_name,
|
|
COUNT(e.emp_id) as employee_count,
|
|
AVG(e.salary) as avg_salary,
|
|
SUM(p.budget) as total_project_budget
|
|
FROM departments d
|
|
LEFT OUTER JOIN employees e ON d.dept_id = e.dept_id
|
|
LEFT OUTER JOIN projects p ON d.dept_id = p.dept_id
|
|
GROUP BY d.dept_name
|
|
ORDER BY d.dept_name;
|
|
|
|
+-------------+----------------+------------+----------------------+
|
|
| dept_name | employee_count | avg_salary | total_project_budget |
|
|
+-------------+----------------+------------+----------------------+
|
|
| Engineering | 2 | 77500.0 | 200000 |
|
|
| HR | 0 | | |
|
|
| Marketing | 1 | 65000.0 | 150000 |
|
|
+-------------+----------------+------------+----------------------+
|
|
|
|
DROP TABLE employees;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE departments;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE projects;
|
|
|
|
Affected Rows: 0
|
|
|