Support for JOIN USING?

[sneakertack]

I was trying to construct queries of the form SELECT * FROM t1 JOIN t2 USING (some_id), but there doesn't seem to be a way for the library to generate such queries. Is there an existing way to do so that I've overlooked?

If it doesn't exist but is desirable, then here are some possible syntax proposals:

// Add a using clause for select statements.
sql.select().from('t1').join('t2').using(['foo_id', 'bar_id']) // Supports multiple columns.
sql.select().from('t1').join('t2').using('foo_id') // String shorthand when only 1 column.

// Overload existing join clause.
sql.select().from('t1').join('t2', ['foo_id']) // Array argument => USING's column list.

On a related note, support for natural joins might also be considered.

[sneakertack]

Reasons for adding USING, instead of just using ON:

Reason 1 (weak): Less typing, no need to explicitly declare ON table1.some_id=table2.some_id if column name is the same.

Reason 2 (strong): Final data returned can end up differing due to duplicate column names. Example:

Given the input:

SELECT * FROM t1 LEFT OUTER JOIN t2 USING ('id')

Possible output:

id	one	two
1	cat	green
2	dog	red
3	giraffe

and then,

Given the input:

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id=t2.id

Possible output:

id	one	two	id
1	cat	green	1
2	dog	red	2
3	giraffe

When using some database connection libraries (e.g. node-postgres) that convert each row into an object, row 3 ends up converting into {id: 3, one: 'giraffe', two: null} in the former case and {one: 'giraffe', two: null, id: null} in the latter, due to duplicate assignment of id. Given that differences exist, library users would benefit from being able to choose between USING and ON, based on their needs.

[prust]

thanks for reporting this @sneakertack! I'm not sure how I missed it, but both USING and NATURAL are specified in SQL-92. Do you want to try your hand at adding one or both to sql-bricks?

[sneakertack]

Sure thing. Hope to deliver positive news in a few days.

[sneakertack]

Current State:

JOIN USING - Ready for merging in this pull request. NATURAL JOIN - Current progress is here. However, halted due to uncertainty over CSNW/sql-bricks#66. While thinking about how to add the new natural join methods I realized that the criteria checks in Join.toString() will likely need to change. What do you advise?