I had worked a lot with Data warehousing and database projects. So many people working told me join is really easy. Left join, right join, Full outer join, cross join and inner join.
I had my opinion no its not easy if you understand it correctly. There is lots of combination that you can do with join and where close play a big role in sql server.
1. Inner join: In definition it says matching records from both table. We can also define Self join which means joining with same table. One more thing to mention if you have only inner joins in your query than no need to use Where clause in most cases. On condition you can use which makes performance of query really faster.
2. Left or Right Outer Join: Left join in terms of definition non matching and matching output from left table and matching output from right table. For the right outer join it is other way around.
Left join and Right join both can serve same purpose. It has been created for better visualization of data. You can have restricted Left join and Restricted Right join if you use where condition with very right purpose.When you have combination of multiple left joins and inner joins in the query than its crucial to identify right output. So when you have this kind of situation work with one join at a time. IF you are using all together than you will not see right output.
3. Full outer join: Full outer join is for all non matching and matching record. If you have multiple full outer join and multiple left join than its a mess.You can also have restricted full outer join.
I have seen lots of issues while joining self join with left outer join or full outer join in single query.
I had my opinion no its not easy if you understand it correctly. There is lots of combination that you can do with join and where close play a big role in sql server.
1. Inner join: In definition it says matching records from both table. We can also define Self join which means joining with same table. One more thing to mention if you have only inner joins in your query than no need to use Where clause in most cases. On condition you can use which makes performance of query really faster.
2. Left or Right Outer Join: Left join in terms of definition non matching and matching output from left table and matching output from right table. For the right outer join it is other way around.
Left join and Right join both can serve same purpose. It has been created for better visualization of data. You can have restricted Left join and Restricted Right join if you use where condition with very right purpose.When you have combination of multiple left joins and inner joins in the query than its crucial to identify right output. So when you have this kind of situation work with one join at a time. IF you are using all together than you will not see right output.
3. Full outer join: Full outer join is for all non matching and matching record. If you have multiple full outer join and multiple left join than its a mess.You can also have restricted full outer join.
I have seen lots of issues while joining self join with left outer join or full outer join in single query.
No comments:
Post a Comment