oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: bin.lb
5
# owner group: SQL2
6
# tags: join
7
# description:
8
#
9
--disable_warnings
10
DROP TABLE IF EXISTS t1,t2;
11
--enable_warnings
12

13
CREATE TABLE t1 (
14
  id smallint NOT NULL,
15
  name varchar(60) DEFAULT '' NOT NULL,
16
  PRIMARY KEY (id)
17
);
18
INSERT INTO t1 VALUES (1,'Antonio Paz');
19
INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
20
INSERT INTO t1 VALUES (3,'Thimble Smith');
21

22
CREATE TABLE t2 (
23
  id smallint NOT NULL,
24
  owner smallint DEFAULT '0' NOT NULL,
25
  name varchar(60),
26
  PRIMARY KEY (id)
27
);
28
INSERT INTO t2 VALUES (1,1,'El Gato');
29
INSERT INTO t2 VALUES (2,1,'Perrito');
30
INSERT INTO t2 VALUES (3,3,'Happy');
31

32
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
33