The most obvious answer everybody will come up with is,
- When I want to get matching recordings between two tables I will use inner join.
When I want to get matching plus unmatchingrecords in the first/second/both tables I will use left/right/full outer join.
But does this answer satisfies everyone?. All through the answer is correct it doesn’t make your seat booked for the post you applied. During interview how you answer is more important than what’s your answer.
It should be like, when tables have “one to one”, “one to many”, “many to one” or “many to many” relationship use inner join. When table have “zero or one to one”,“zero or one to many”,“many to zero or one”,“zero or many to many” we will use outer join.
Both the answers are correct. But in “3 idiots” movie a very good dialogue was used “some don’t like simple answers” and it even applies during interviews.
Hope this trick help you guys.
Below is a nice video SQL Server interview question video on inner joins , left joins and outer joins.