Is there any natural join replacement in SQL Server 2005?

Question

Hi, I have two tables which I would like to join by ID field. I was trying to use for this "INNER JOIN". Everything would be good but there are two issues:

1. As a result I receive twice column ID.
2. I have to omit specifying columns which should be displayed under select statement. I would like to use there a *.

I red that other sql-s have something like natural join and that is probably (or not?) an answer for my question. Unfortunately there is no join like that in SQL Server (2005). Do anybody knows any good replacement of it?

Answer 1

SQL Server does not have the natural join, just list out all the columns you want. (you can drag the table column folder in SSMS to the code window and all the columns for the table will be listed, then you just delete the ones you don't want)

Answer 2

I think you want

SELECT * FROM x INNER JOIN y ON x.id = y.id

But you want to skip y.id (because it duplicates x.id).

If this is correct: no, there's no way to do this in any dialect of SQL I know. You'll have to say

SELECT x.*, y.col1, y.col2, ... FROM x INNER JOIN y ON x.id = y.id