DROP DATABASE IF EXISTS mcol641_joins_db;
CREATE DATABASE mcol641_joins_db;
USE mcol641_joins_db;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38)) ENGINE=columnstore;
CREATE TABLE cs2 (de1 DECIMAL(38,38), de2 DECIMAL(38,10)) ENGINE=columnstore;
INSERT INTO cs1 values (99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998);
INSERT INTO cs1 VALUES (12345678901234567890123456789012345678, 1234567890123456789012345678.1234567890, 0.12345678901234567890123456789012345678);
INSERT INTO cs1 VALUES (-12345678901234567890123456789012345678, -1234567890123456789012345678.1234567890, -0.12345678901234567890123456789012345678);
INSERT INTO cs1 VALUES (0, 1.1234567891, 0.1234567890123456789012345678912345);
INSERT INTO cs2 values (0.99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998);
INSERT INTO cs2 VALUES (0.12345678901234567890123456789012345678, 1234567890123456789012345678.0123456789);
INSERT INTO cs2 VALUES (-0.12345678901234567890123456789012345678, -1234567890123456789012345678.0123456789);
INSERT INTO cs2 VALUES (0.12345678901234567890123456789, 1.1234567891);
SELECT cs1.d1, cs1.d2, cs2.de1 FROM cs1 INNER JOIN cs2 ON cs1.d2 = cs2.de2 ORDER BY 1;
d1	d2	de1
0	1.1234567891	0.12345678901234567890123456789000000000
99999999999999999999999999999999999998	9999999999999999999999999999.9999999998	0.99999999999999999999999999999999999998
SELECT cs2.de1, cs1.d1, cs1.d2 FROM cs1 INNER JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
de1	d1	d2
-0.12345678901234567890123456789012345678	-12345678901234567890123456789012345678	-1234567890123456789012345678.1234567890
0.12345678901234567890123456789012345678	12345678901234567890123456789012345678	1234567890123456789012345678.1234567890
0.99999999999999999999999999999999999998	99999999999999999999999999999999999998	9999999999999999999999999999.9999999998
SELECT cs2.de2, cs1.d1, cs1.d3, cs2.de1 FROM cs1 LEFT JOIN cs2 ON cs1.d2 = cs2.de2 ORDER BY 2;
de2	d1	d3	de1
NULL	-12345678901234567890123456789012345678	-0.12345678901234567890123456789012345678	NULL
1.1234567891	0	0.12345678901234567890123456789123450000	0.12345678901234567890123456789000000000
NULL	12345678901234567890123456789012345678	0.12345678901234567890123456789012345678	NULL
9999999999999999999999999999.9999999998	99999999999999999999999999999999999998	0.99999999999999999999999999999999999998	0.99999999999999999999999999999999999998
SELECT cs1.d3, cs1.d1, cs1.d2, cs2.de2 FROM cs1 LEFT JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
d3	d1	d2	de2
-0.12345678901234567890123456789012345678	-12345678901234567890123456789012345678	-1234567890123456789012345678.1234567890	-1234567890123456789012345678.0123456789
0.12345678901234567890123456789012345678	12345678901234567890123456789012345678	1234567890123456789012345678.1234567890	1234567890123456789012345678.0123456789
0.12345678901234567890123456789123450000	0	1.1234567891	NULL
0.99999999999999999999999999999999999998	99999999999999999999999999999999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998
SELECT cs1.d1, cs1.d3, cs2.de1, cs2.de2 FROM cs1 RIGHT JOIN cs2 ON cs1.d2 = cs2.de2 ORDER BY 3;
d1	d3	de1	de2
NULL	NULL	-0.12345678901234567890123456789012345678	-1234567890123456789012345678.0123456789
0	0.12345678901234567890123456789123450000	0.12345678901234567890123456789000000000	1.1234567891
NULL	NULL	0.12345678901234567890123456789012345678	1234567890123456789012345678.0123456789
99999999999999999999999999999999999998	0.99999999999999999999999999999999999998	0.99999999999999999999999999999999999998	9999999999999999999999999999.9999999998
SELECT cs2.de1, cs2.de2, cs1.d1, cs1.d2 FROM cs1 RIGHT JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
de1	de2	d1	d2
-0.12345678901234567890123456789012345678	-1234567890123456789012345678.0123456789	-12345678901234567890123456789012345678	-1234567890123456789012345678.1234567890
0.12345678901234567890123456789000000000	1.1234567891	NULL	NULL
0.12345678901234567890123456789012345678	1234567890123456789012345678.0123456789	12345678901234567890123456789012345678	1234567890123456789012345678.1234567890
0.99999999999999999999999999999999999998	9999999999999999999999999999.9999999998	99999999999999999999999999999999999998	9999999999999999999999999999.9999999998
SELECT * FROM cs1 WHERE cs1.d2 IN (SELECT cs2.de2 FROM cs2 WHERE cs2.de2 = cs1.d2) ORDER BY 1;
d1	d2	d3
0	1.1234567891	0.12345678901234567890123456789123450000
99999999999999999999999999999999999998	9999999999999999999999999999.9999999998	0.99999999999999999999999999999999999998
SELECT * FROM cs2 WHERE cs2.de2 IN (SELECT cs1.d2 FROM cs1 WHERE cs1.d2 = cs2.de2) ORDER BY 1;
de1	de2
0.12345678901234567890123456789000000000	1.1234567891
0.99999999999999999999999999999999999998	9999999999999999999999999999.9999999998
SELECT * FROM cs1 WHERE cs1.d3 IN (SELECT cs2.de1 FROM cs2 WHERE cs2.de1 = cs1.d3) ORDER BY 1;
d1	d2	d3
-12345678901234567890123456789012345678	-1234567890123456789012345678.1234567890	-0.12345678901234567890123456789012345678
12345678901234567890123456789012345678	1234567890123456789012345678.1234567890	0.12345678901234567890123456789012345678
99999999999999999999999999999999999998	9999999999999999999999999999.9999999998	0.99999999999999999999999999999999999998
SELECT * FROM cs2 WHERE cs2.de1 IN (SELECT cs1.d3 FROM cs1 WHERE cs1.d3 = cs2.de1) ORDER BY 1;
de1	de2
-0.12345678901234567890123456789012345678	-1234567890123456789012345678.0123456789
0.12345678901234567890123456789012345678	1234567890123456789012345678.0123456789
0.99999999999999999999999999999999999998	9999999999999999999999999999.9999999998
SELECT count(*) FROM cs1 where exists (SELECT cs2.de2 FROM cs2 WHERE cs1.d2=cs2.de2);
count(*)
2
SELECT count(*) FROM cs2 where not exists (SELECT cs1.d2 FROM cs1 WHERE cs1.d2=cs2.de2);
count(*)
2
DROP DATABASE mcol641_joins_db;