USING and NATURAL JOIN support

[mdigger]

What do you want to change?

-- name: GetAllBooks :one
SELECT *
FROM books
JOIN authors
USING (author_id);

-- name: GetAllBooks2 :one
SELECT *
FROM books
NATURAL JOIN authors;

type GetAllBooksRow struct {
	BookID     int64
	Title      string
	AuthorID   int64
	AuthorID_2 int64
	Name       string
	Bio        sql.NullString
}

type GetAllBooks2Row struct {
	BookID     int64
	Title      string
	AuthorID   int64
	AuthorID_2 int64
	Name       string
	Bio        sql.NullString
}

https://play.sqlc.dev/p/af741ebf13099ebea32d4b4c71753161c7a5d34e85030863bdae141e72b6e403

The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one. For example, joining T1 and T2 with USING (a, b) produces the join condition ON T1.a = T2.a AND T1.b = T2.b.

Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables. As with USING, these columns appear only once in the output table. If there are no common column names, NATURAL JOIN behaves like JOIN ... ON TRUE, producing a cross-product join.

What database engines need to be changed?

No response

What programming language backends need to be changed?

Go