DROP DATABASE IF EXISTS mcs81_db;
CREATE DATABASE mcs81_db;
USE mcs81_db;
CREATE TABLE t1 (t1_col1 INT, t1_col2 TEXT)ENGINE=Columnstore;
INSERT INTO t1 VALUES (NULL, NULL);
INSERT INTO t1 VALUES (1, repeat('a', 20)),(3, repeat('c', 20)),(5, repeat('a', 20)),(7, repeat('c', 20)),(9, repeat('a', 20));
SELECT * FROM t1 AS a, t1 AS b WHERE a.t1_col1 = b.t1_col1;
t1_col1	t1_col2	t1_col1	t1_col2
1	aaaaaaaaaaaaaaaaaaaa	1	aaaaaaaaaaaaaaaaaaaa
3	cccccccccccccccccccc	3	cccccccccccccccccccc
5	aaaaaaaaaaaaaaaaaaaa	5	aaaaaaaaaaaaaaaaaaaa
7	cccccccccccccccccccc	7	cccccccccccccccccccc
9	aaaaaaaaaaaaaaaaaaaa	9	aaaaaaaaaaaaaaaaaaaa
SELECT a.t1_col1,b.t1_col2 FROM t1 AS a, t1 AS b WHERE a.t1_col1 = b.t1_col1;
t1_col1	t1_col2
1	aaaaaaaaaaaaaaaaaaaa
3	cccccccccccccccccccc
5	aaaaaaaaaaaaaaaaaaaa
7	cccccccccccccccccccc
9	aaaaaaaaaaaaaaaaaaaa
SELECT a.t1_col1 AS a_col1, b.t1_col2 AS b_col2 FROM t1 AS a, t1 AS b WHERE a.t1_col1 = b.t1_col1 ORDER BY a.t1_col1;
a_col1	b_col2
1	aaaaaaaaaaaaaaaaaaaa
3	cccccccccccccccccccc
5	aaaaaaaaaaaaaaaaaaaa
7	cccccccccccccccccccc
9	aaaaaaaaaaaaaaaaaaaa
SELECT * FROM t1 a JOIN t1 b ON a.t1_col1 = b.t1_col1;
t1_col1	t1_col2	t1_col1	t1_col2
1	aaaaaaaaaaaaaaaaaaaa	1	aaaaaaaaaaaaaaaaaaaa
3	cccccccccccccccccccc	3	cccccccccccccccccccc
5	aaaaaaaaaaaaaaaaaaaa	5	aaaaaaaaaaaaaaaaaaaa
7	cccccccccccccccccccc	7	cccccccccccccccccccc
9	aaaaaaaaaaaaaaaaaaaa	9	aaaaaaaaaaaaaaaaaaaa
SELECT a.t1_col1,b.t1_col2 FROM t1 a JOIN t1 b ON a.t1_col1 = b.t1_col1;
t1_col1	t1_col2
1	aaaaaaaaaaaaaaaaaaaa
3	cccccccccccccccccccc
5	aaaaaaaaaaaaaaaaaaaa
7	cccccccccccccccccccc
9	aaaaaaaaaaaaaaaaaaaa
SELECT a.t1_col1 AS a_col1, b.t1_col2 AS b_col2 FROM t1 a JOIN t1 b ON a.t1_col1 = b.t1_col1 ORDER BY b.t1_col1;
a_col1	b_col2
1	aaaaaaaaaaaaaaaaaaaa
3	cccccccccccccccccccc
5	aaaaaaaaaaaaaaaaaaaa
7	cccccccccccccccccccc
9	aaaaaaaaaaaaaaaaaaaa
SELECT * FROM t1 a INNER JOIN t1 b ON a.t1_col1 = b.t1_col1 AND b.t1_col1 > 5;
t1_col1	t1_col2	t1_col1	t1_col2
7	cccccccccccccccccccc	7	cccccccccccccccccccc
9	aaaaaaaaaaaaaaaaaaaa	9	aaaaaaaaaaaaaaaaaaaa
SELECT * FROM t1 a LEFT JOIN t1 b ON a.t1_col1 = b.t1_col1 AND a.t1_col2 LIKE '%a%' ORDER BY a.t1_col1;
t1_col1	t1_col2	t1_col1	t1_col2
NULL	NULL	NULL	NULL
1	aaaaaaaaaaaaaaaaaaaa	1	aaaaaaaaaaaaaaaaaaaa
3	cccccccccccccccccccc	NULL	NULL
5	aaaaaaaaaaaaaaaaaaaa	5	aaaaaaaaaaaaaaaaaaaa
7	cccccccccccccccccccc	NULL	NULL
9	aaaaaaaaaaaaaaaaaaaa	9	aaaaaaaaaaaaaaaaaaaa
DROP DATABASE mcs81_db;