Join two tables but want one matching columns instead of showing it separately sql server 2008

Question

Hi i want to display join result of two tables but don't want to show matching column separately. I want it one instead of two. So please tell me what query should i use for this. I am using SQL Server 2008 and my query is like:

select * 
from   Customer_Order, optRelation 
where  Customer_Order.orderNumber = optRelation.orderNumber AND 
       optRelation.orderNumber = 21

Answer 1

Never use SELECT * in production code. Specify the columns you want.

Answer 2

You can simply specify the fields you want instead of using SELECT *. In fact, using SELECT * is considered bad practice for various reasons:

SELECT customer_Order.OrderNumber, 
       customer_Order.SomeFieldA,
       customer_Order.SomeFieldB,
       customer_Order.SomeFieldB,
       optRelation.SomeOtherField1,
       optRelation.SomeOtherField2,
       optRelation.SomeOtherField3
FROM   customer_Order, optRelation 
WHERE  customer_Order.OrderNumber = optRelation.OrderNumber AND 
       optRelation.OrderNumber = 21

You can also, (but you also shouldn't, in general) select all the fields of one table, and then explicitly select the fields of the second table:

SELECT customer_Order.*,
       optRelation.SomeOtherField1,
       optRelation.SomeOtherField2,
       optRelation.SomeOtherField3
FROM   customer_Order, optRelation 
WHERE  customer_Order.OrderNumber = optRelation.OrderNumber AND 
       optRelation.OrderNumber = 21