What will happen if we execute below queries?

Select emp_id,employee_name from employee_master e
inner join employee_address ea
on e.emp_id = ea.emp_id;

Select emp_id,emp_id,employee_name from employee_master e
inner join employee_address ea
on e.emp_id = ea.emp_id;

 Posted by vishalneeraj-24503 on 7/2/2014 | Category: Sql Server Interview questions | Views: 341 | Points: 40
Answer:

It will give us error as Ambiguous column name 'emp_id'.Because 'emp_id' is present on both tables i.e. employee_master and employee_address table that's why when we execute above queries,tables will confuse which emp_id is refering to which Tables,then it will say Ambiguous column name or duplicate column name.

To overcome this problem just provide alias name in columns as below:-
Select e.emp_id,employee_name from employee_master e

inner join employee_address ea
on e.emp_id = ea.emp_id;

Select e.emp_id,ea.emp_id,employee_name from employee_master e
inner join employee_address ea
on e.emp_id = ea.emp_id;


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response