CODEWITHSUNDEEP
sqlsql-servert-sql
Alexander Stalt
Alexander Stalt

Reputation: 977

Select proper columns from JOIN statement

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 ?

Upvotes: 17

Views: 29429

Answers (3)

marc_s
marc_s

Reputation: 755043

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.

Upvotes: 9

Tomas Vana
Tomas Vana

Reputation: 18775

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

Upvotes: -1

Daniel Vassallo
Daniel Vassallo

Reputation: 344431

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

Upvotes: 20

Related Questions