Are there any succinct approaches to this left join?

Question

A simple inner join

select * from table1 a
inner join 
dbo.table2 b
on a.inventory_id = b.inventory_id

Wouldn't it be nice and intuitive to put it like this?

select * from table1 a
inner join 
dbo.table2 b
on inventory_id

Are there any comparable succinct approaches?

Thanks!

I hope that the `SELECT *` is for illustration, and not used in practice. — Kermit, Nov 08 '12 at 19:25
It would only be succinct if both key fields were named the same. I'd rather spell them out then if I need to change them in the future I don't need to change the syntax. — Jesse, Nov 08 '12 at 19:27

score 2 · Answer 1 · answered Nov 08 '12 at 19:33

If you were using PostgreSQL, MySQL or Oracle, you can use a Natural Join

   select *
     from table1 a
  natural join table2 b

Not sure why the question title includes "left", but you can do a natural left join as well.

Unfortunately, I'm sure you're using SQL Server due to the dbo., so no, the ON condition is required.

score 1 · Accepted Answer · edited May 23 '17 at 12:27

How about a natural join:

select *
from table1 a
natural join dbo.table2 b

However, your RDBMS may not support it, and I would recommend always specifying the join type and conditions in your queries. It's much more maintainable in the long run.

I'm guessing from the dbo. that you're using SQL Server though, and it's not supported there. See here for more info.

Edit:
There's another possibility that's again not supported by SQL Server but is worth noting. This could actually be worth using, as your join condition is clearly specified. More info here.

select *
from table1
inner join dbo.table2 using (inventory_id)

score 0 · Answer 3 · answered Nov 08 '12 at 19:26

0

If you don't want to use ANSI standard JOINs, then use implicit syntax:

select * from table1 a, table2 b
where a.inventory_id = b.inventory_id

answered Nov 08 '12 at 19:26

Kermit

33,827
13
85
121

Are there any succinct approaches to this left join?

3 Answers3