Select proper columns from JOIN statement

Question

I have two tables: table1, table2. Table1 has 10 columns, table2 has 2 columns.

SELECT * FROM table1 AS T1 INNER JOIN table2 AS T2 ON T1.ID = T2.ID

I want to select all columns from table1 and only 1 column from table2. Is it possible to do that without enumerating all columns from table1 ?

Answer 1

Yes, you can do the following:

SELECT t1.*, t2.my_col FROM table1 AS T1 INNER JOIN table2 AS T2 ON T1.ID = T2.ID

Answer 2

Use table1.* in place of all columns of table1 ;)

Answer 3

Even though you can do the t1.*, t2.col1 thing, I would not recommend it in production code.

I would never ever use a SELECT * in production - why?

• you're telling SQL Server to get all columns - do you really, really need all of them?
• by not specifying the column names, SQL Server has to go figure that out itself - it has to consult the data dictionary to find out what columns are present which does cost a little bit of performance
• most importantly: you don't know what you're getting back. Suddenly, the table changes, another column or two are added. If you have any code which relies on e.g. the sequence or the number of columns in the table without explicitly checking for that, your code can brake

My recommendation for production code: always (no exceptions!) specify exactly those columns you really need - and even if you need all of them, spell it out explicitly. Less surprises, less bugs to hunt for, if anything ever changes in the underlying table.