github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/join/join.result (about) 1 drop table if exists t1; 2 drop table if exists t2; 3 CREATE TABLE t1 (S1 INT); 4 CREATE TABLE t2 (S1 INT); 5 INSERT INTO t1 VALUES (1),(3),(4),(6); 6 INSERT INTO t2 VALUES (2),(4),(5); 7 SELECT * FROM t1 JOIN t2 on t1.S1=t2.S1; 8 S1 S1 9 4 4 10 drop table if exists t1; 11 drop table if exists t2; 12 create table t1 (id int); 13 create table t2 (id int); 14 insert into t1 values (75); 15 insert into t1 values (79); 16 insert into t1 values (78); 17 insert into t1 values (77); 18 insert into t1 values (76); 19 insert into t1 values (76); 20 insert into t1 values (104); 21 insert into t1 values (103); 22 insert into t1 values (102); 23 insert into t1 values (101); 24 insert into t1 values (105); 25 insert into t1 values (106); 26 insert into t1 values (107); 27 insert into t2 values (107),(75),(1000); 28 select t1.id, t2.id from t1, t2 where t2.id = t1.id; 29 id id 30 75 75 31 107 107 32 select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id; 33 id count(t2.id) 34 75 1 35 107 1 36 select t1.id,t2.id from t2 join t1 on t1.id=t2.id where t2.id=75; 37 id id 38 75 75 39 drop table if exists t1; 40 drop table if exists t2; 41 CREATE TABLE t1 ( 42 id int, 43 token varchar(100) DEFAULT '' NOT NULL, 44 count int DEFAULT 0 NOT NULL, 45 qty int, 46 phone char(1) DEFAULT '' NOT NULL, 47 times datetime DEFAULT '2000-01-01 00:00:00' NOT NULL 48 ); 49 INSERT INTO t1 VALUES (21,'e45703b64de71482360de8fec94c3ade',3,7800,'n','1999-12-23 17:22:21'); 50 INSERT INTO t1 VALUES (22,'e45703b64de71482360de8fec94c3ade',4,5000,'y','1999-12-23 17:22:21'); 51 INSERT INTO t1 VALUES (18,'346d1cb63c89285b2351f0ca4de40eda',3,13200,'b','1999-12-23 11:58:04'); 52 INSERT INTO t1 VALUES (17,'ca6ddeb689e1b48a04146b1b5b6f936a',4,15000,'b','1999-12-23 11:36:53'); 53 INSERT INTO t1 VALUES (16,'ca6ddeb689e1b48a04146b1b5b6f936a',3,13200,'b','1999-12-23 11:36:53'); 54 INSERT INTO t1 VALUES (26,'a71250b7ed780f6ef3185bfffe027983',5,1500,'b','1999-12-27 09:44:24'); 55 INSERT INTO t1 VALUES (24,'4d75906f3c37ecff478a1eb56637aa09',3,5400,'y','1999-12-23 17:29:12'); 56 INSERT INTO t1 VALUES (25,'4d75906f3c37ecff478a1eb56637aa09',4,6500,'y','1999-12-23 17:29:12'); 57 INSERT INTO t1 VALUES (27,'a71250b7ed780f6ef3185bfffe027983',3,6200,'b','1999-12-27 09:44:24'); 58 INSERT INTO t1 VALUES (28,'a71250b7ed780f6ef3185bfffe027983',3,5400,'y','1999-12-27 09:44:36'); 59 INSERT INTO t1 VALUES (29,'a71250b7ed780f6ef3185bfffe027983',4,17700,'b','1999-12-27 09:45:05'); 60 CREATE TABLE t2 ( 61 id int, 62 category int DEFAULT 0 NOT NULL, 63 county int DEFAULT 0 NOT NULL, 64 state int DEFAULT 0 NOT NULL, 65 phones int DEFAULT 0 NOT NULL, 66 nophones int DEFAULT 0 NOT NULL 67 ); 68 INSERT INTO t2 VALUES (3,2,11,12,5400,7800); 69 INSERT INTO t2 VALUES (4,2,25,12,6500,11200); 70 INSERT INTO t2 VALUES (5,1,37,6,10000,12000); 71 select t1.id, category as catid, state as stateid, county as countyid from t1 join t2 on count=t2.id where token='a71250b7ed780f6ef3185bfffe027983'; 72 id catid stateid countyid 73 28 2 12 11 74 27 2 12 11 75 29 2 12 25 76 26 1 6 37 77 select t1.id, category as catid, state as stateid, county as countyid from t1 join t2 on count=t2.id where token='a71250b7ed780f6ef3185bfffe027983' and t1.id>26 order by t1.id; 78 id catid stateid countyid 79 27 2 12 11 80 28 2 12 11 81 29 2 12 25 82 drop table if exists t1; 83 drop table if exists t2; 84 drop table if exists t3; 85 CREATE TABLE t1 ( 86 t1_id int default NULL, 87 t2_id int default NULL, 88 type varchar(12) default NULL, 89 cost_unit varchar(5) default NULL, 90 min_value double default NULL, 91 max_value double default NULL, 92 t3_id int default NULL, 93 item_id int default NULL 94 ); 95 CREATE TABLE t2 ( 96 id int NOT NULL, 97 name varchar(255) default NULL, 98 PRIMARY KEY (id) 99 ); 100 INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1),(14,4,'Percent','Cost',-1,0,-1,-1),(18,5,'Percent','Cost',-1,0,-1,-1),(19,4,'Percent','Cost',-1,0,-1,-1),(20,5,'Percent','Cost',100,-1,22,291),(21,5,'Percent','Cost',100,-1,18,291),(22,1,'Percent','Cost',100,-1,6,291),(23,1,'Percent','Cost',100,-1,21,291),(24,1,'Percent','Cost',100,-1,9,291),(25,1,'Percent','Cost',100,-1,4,291),(26,1,'Percent','Cost',100,-1,20,291),(27,4,'Percent','Cost',100,-1,7,202),(28,1,'Percent','Cost',50,-1,-1,137),(29,2,'Percent','Cost',100,-1,4,354),(30,2,'Percent','Cost',100,-1,9,137),(93,2,'Cost','Cost',-1,10000000,-1,-1); 101 INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5'); 102 select t2_id,name, type from t1 join t2 on t2.id=t1.t2_id order by id; 103 t2_id name type 104 1 s1 Percent 105 1 s1 Percent 106 1 s1 Percent 107 1 s1 Percent 108 1 s1 Percent 109 1 s1 Percent 110 2 s2 Percent 111 2 s2 Percent 112 2 s2 Cost 113 4 s4 Percent 114 4 s4 Percent 115 4 s4 Percent 116 5 s5 Percent 117 5 s5 Percent 118 5 s5 Percent 119 5 s5 Percent 120 drop table t1; 121 drop table t2; 122 CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255)); 123 CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255)); 124 INSERT INTO t1 VALUES (1, 'A'); 125 INSERT INTO t2 VALUES (1, 'B'); 126 SELECT t1.ID,Value2 FROM t1 JOIN t2 on t1.ID=t2.ID WHERE Value1 = 'A'; 127 ID Value2 128 1 B 129 SELECT t1.ID,Value2 FROM t1 JOIN t2 on t1.ID=t2.ID WHERE Value1 = 'A' and Value2 <> 'B'; 130 ID Value2 131 drop table if exists t1; 132 drop table if exists t2; 133 drop table if exists t3; 134 CREATE TABLE t1 (a int); 135 CREATE TABLE t2 (b int); 136 CREATE TABLE t3 (c int); 137 insert into t1 values(1),(2),(3),(4),(5); 138 insert into t2 values(1),(3),(5),(7),(9); 139 insert into t3 values(1),(1),(3),(4),(7); 140 select a,b,c from t1 join t2 on t1.a=t2.b join t3 on t1.a=t3.c where a>1; 141 a b c 142 3 3 3 143 drop table if exists t1; 144 drop table if exists t2; 145 drop table if exists t3; 146 create table t1 (i int); 147 create table t2 (i int); 148 create table t3 (i int); 149 insert into t1 values(1),(2); 150 insert into t2 values(2),(3); 151 insert into t3 values (2),(4); 152 select t3.i from t1 join t2 on t1.i=t2.i join t3 on t2.i=t3.i; 153 i 154 2 155 drop table if exists t1; 156 drop table if exists t2; 157 drop table if exists t3; 158 drop table if exists t4; 159 drop table if exists t5; 160 drop table if exists t6; 161 create table t1 (c int, b int); 162 create table t2 (a int, b int); 163 create table t3 (b int, c int); 164 create table t4 (y int, c int); 165 create table t5 (y int, z int); 166 create table t6 (a int, c int); 167 insert into t1 values (10,1); 168 insert into t1 values (3 ,1); 169 insert into t1 values (3 ,2); 170 insert into t2 values (2, 1); 171 insert into t3 values (1, 3); 172 insert into t3 values (1,10); 173 insert into t4 values (11,3); 174 insert into t4 values (2, 3); 175 insert into t5 values (11,4); 176 insert into t6 values (2, 3); 177 select distinct a,t1.b,t3.c from t1 join t2 on t1.b=t2.b join t3 on t1.b=t3.c; 178 a b c 179 drop table if exists t1; 180 drop table if exists t2; 181 drop table if exists t3; 182 drop table if exists t4; 183 create table t1 (a1 int, a2 int); 184 create table t2 (a1 int, b int); 185 create table t3 (c1 int, c2 int); 186 create table t4 (c2 int); 187 insert into t1 values (1,1); 188 insert into t2 values (1,1); 189 insert into t3 values (1,1); 190 insert into t4 values (1); 191 select * from t1 join t2 on t1.a1=t2.a1 join t3 on b=c1 join t4 on t3.c2=t4.c2; 192 a1 a2 a1 b c1 c2 c2 193 1 1 1 1 1 1 1 194 drop table if exists t1; 195 create table t1 (a int); 196 insert into t1 values(1); 197 drop table if exists t2; 198 create table t2 (a int); 199 insert into t2 values(1); 200 select * from (t1 join t2 on t1.a = t2.a); 201 a a 202 1 1 203 drop table if exists tt; 204 drop table if exists tt2; 205 create table tt(a text,b text,c int); 206 create table tt2(a text,b text,c int); 207 insert into tt values("a","bc",1); 208 insert into tt2 values("ab","c",2); 209 select * from tt as t11 join tt2 as t12 on t11.a = t12.a and t11.b = t12.b; 210 a b c a b c 211 drop table if exists t1; 212 drop table if exists t2; 213 drop table if exists t3; 214 create table t1 (q int); 215 create table t2 (a int); 216 create table t3 (b int); 217 select * from t1,t2 join t3 on a=b; 218 q a b