Overview:
In this article I would like to explain about Joins. I have seen many articles and many sites where they had given detailed explanation about joins, but not really on what are ANSI joins and difference between so called “Legacy Joins”. In this article my main motto is to give you syntax differences.
Introduction:
It’s very obvious that you want to upgrade/migrate the code which in lower version to higher version. When you are migrating/upgrading the code you want to use the new features in the latest version. There will be so many reasons why you want to go with new features. What we're going to look at in this article is what many people still consider to be the "normal" way of coding joins. I would highly recommend that you use the ANSI method since it has much better portability between systems and is also much more readable. It is worth noting that the old syntax is actually reasonably well supported across platforms at the current time, but the ANSI syntax is now also supported by every major platform out there.
INNER JOIN:
Let's look at the very basic join, which is INNER or EQUI JOIN
SELECT E.*
FROM HRDETAILS.EMP E
INNER JOIN HRDETAILS.EMP M
ON E.MGRID = M.EMPID;
The above query is based on ANSI joins. Now let's rewrite this query using a WHERE clause–based join syntax.
It's very simple just replace "INNER JOIN" with "," and then where ever you have ON condition replace that with either WHERE or AND condition depending on the conditions and joins in your query.
SELECT E.*
FROM HRDETAILS.EMP E ,
HRDETAILS.EMP M
WHERE E.MGRID = M.EMPID;
There will not be any difference in the out put.
OUTER JOIN:
Let's look at the most widely used join, which is OUTER join. I will not focus on what are outer joins but will give how to us ANSI joins to write OUTER join queries.
There are two types of outer joins:
1) Left Outer Join or Left Join: *= symbol is used to implement this kind of join.
2) Right Outer Join or Right Join: =* symbol is used to implement this kind of join.
SELECT E.EMPID, M.EMPID AS ManagerID
FROM HRDETAILS.EMP E
LEFT OUTER JOIN HRDETAILS.EMP M
ON E.MGRID = M.EMPID;
The above query is based on ANSI joins. Now let's rewrite this query using a WHERE clause–based join syntax.
It's very simple just replace "LEFT OUTER JOIN" with "," and then where ever you have ON condition replace that with either WHERE or AND condition depending on the conditions and joins in your query.
SELECT E.EMPID, M.EMPID AS ManagerID
FROM HRDETAILS.EMP E ,
HRDETAILS.EMP M
WHERE E.MGRID *= M.EMPID;
There will not be any difference in the out put.
CROSS JOIN:
Let's look at the very rarely used join, which is CROSS join. The simplest ways of implementing the CROSS JOIN is remove the joining conditions on these tables.
So, for an ultra-quick example, let's take our first example from the CROSS JOIN section earlier in the chapter. The ANSI syntax looked like this:
SELECT *
FROM CARMODELS CM
CROSS JOIN COLORS C;
To convert it to the old syntax, we just strip out the CROSS JOIN keywords and add a comma:
SELECT *
FROM CARMODELS CM, COLORS C;
Summary:
These ANSI joins are supported major Database providers like Oracle, Sybase, DB2 Etc.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|