outer_join.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

statement ok
CREATE TABLE a(a INTEGER);

statement ok
CREATE TABLE b(b INTEGER);

statement ok
CREATE TABLE c(c INTEGER);

statement ok
INSERT INTO a VALUES (1);

statement ok
INSERT INTO b VALUES (2);

statement ok
INSERT INTO c VALUES (3);

query III
SELECT * FROM a, b full join c on b = c;
----
1  NULL  3
1  2  NULL

query III
SELECT * FROM a, b right join c on b = c;
----
1  NULL  3

query III
SELECT * FROM a, b left join c on b = c;
----
1  2  NULL

query III
SELECT * FROM a CROSS JOIN b JOIN LATERAL(SELECT a.a FROM c) x ON TRUE;
----
1 2 1

query III
SELECT * FROM a, b FULL JOIN LATERAL(SELECT a.a FROM c) x ON TRUE;
----
1 2 1

query III
SELECT * FROM a CROSS JOIN (b FULL JOIN LATERAL(SELECT a.a FROM c) x ON TRUE);
----
1 2 1

statement ok
CREATE TABLE t1 (a int, b int);

statement ok
CREATE TABLE t2 (a int, c int);

statement ok
INSERT INTO t1 VALUES (1, 2), (2, 3);

statement ok
INSERT INTO t2 VALUES (2, 4), (5, 7);

query IIII rowsort
SELECT * FROM generate_series(1, 2), LATERAL (SELECT * FROM t1) _ NATURAL RIGHT JOIN t2;
----
1 2 3    4
2 2 3    4
1 5 NULL 7
2 5 NULL 7