how to write combination of RIGHT OUTER JOIN and LEFT OUTER JOIN

Question

Could any one help me to resolve the below query..

Here we have 1. Left outer join 2. Two right outer join

how to combine both right and left O.J and create the same effect as above ,

I need the same query rewritten , this is because I am converting our old ms SQL2009 to ms SQL2012.. and in 2012 *= , =* is not supported.

SELECT X.master_key, X.vendor_code  
FROM 
X, Y, X x2 
WHERE Y.master_key = X.parent_key  
AND Y.master_key *= x2.parent_key 
AND x2.INITIALS =* Y.DEFAULT_INITIALS 
AND x2.VENDOR_CODE =* Y.VENDOR_ABBREV 
AND Y.project_name = 'TEST'  

I have modified above code with my basic knowledge as below and which is not working at all

SELECT X.master_key, X.vendor_code  
FROM 
X, 
Y left outer join X x2 
on Y.master_key = x2.parent_key,

X vnd RIGHT OUTER JOIN Y vnm 
(on vnd.INITIALS = vnm.DEFAULT_INITIALS AND vnd.VENDOR_CODE = vnm.VENDOR_ABBREV )

WHERE Y.master_key = X.parent_key 
AND Y.project_name = 'TEST'  

I am not getting the desired values as original query has to do.

Below is my full original query..

SELECT vnd.master_key, vnd.vendor_code  
FROM 
vnd, vnm, vnd vn2 
WHERE vnm.master_key = vnd.parent_key  
AND vnm.master_key *= vn2.parent_key 
AND vn2.INITIALS =* vnm.DEFAULT_INITIALS 
AND vn2.VENDOR_CODE =* vnm.VENDOR_ABBREV 
AND vnm.inactive = 0 
AND vnd.inactive = 0 
AND vnm.project_name = 'TEST'  
ORDER BY 
lower(vnm.company_name   +  ' '), lower(vnd.vendor_code)  ,lower(vnd.first_name   +  ' '), lower(vnd.last_name   +  ' ')

The solution you gave works but could you do the above one too

SELECT X.master_key, X.vendor_code  
FROM X 
INNER JOIN Y ON X.parent_key = Y.master_key 
LEFT OUTER JOIN X AS x2 ON x2.parent_key = Y.master_key AND x2.INITIALS = Y.DEFAULT_INITIALS AND x2.VENDOR_CODE = Y.VENDOR_ABBREV 
WHERE Y.project_name = 'TEST' 

Regards Janaki

Answer 1

You don't need both LEFT and RIGHT join, since the outer side is always the x2 alias of the X table.

It's actually not possible to LEFT and RIGHT join at the same time to the same table (alias), if by these you mean that both sides of the same join need to function as the inner table of a join predicate. However, you can always FULL JOIN if you are interested in having all rows from both tables.

In the meantime, your query can be converted to the following:

SELECT
   X.master_key,
   X.vendor_code  
FROM 
   X
   INNER JOIN Y
      ON Y.master_key = X.parent_key
   LEFT JOIN X x2
      ON Y.master_key = x2.parent_key
      AND Y.DEFAULT_INITIALS = x2.INITIALS
      AND Y.VENDOR_ABBREV = x2.VENDOR_CODE
WHERE
   Y.project_name = 'TEST'   
;

An INNER JOIN is the normal result of a join between two tables: only the rows that have a match in both tables are included in the result. It is best to use ANSI-style joins instead of continuing to put the join condition in the WHERE clause, even if that is possible. It is then clearer which conditions are just for this query's unique data-filtering activity, and which are the more global conditions for joining the particular tables.

Some more explanation of LEFT and RIGHT join related to the old-style syntax: the old way, the 'inner' side was expressed in each condition by the asterisk being on that side. In ANSI joins, the join semantics are stated at the table level. This means that all the join predicates had to be converted to either LEFT, or RIGHT, but not both.

In fact, there is no difference between LEFT and RIGHT join except for join order. The following two queries are identical in meaning:

SELECT *
FROM
   TableA
   LEFT JOIN TableB
      ON A.ID = B.ID
;

SELECT *
FROM
   TableB
   RIGHT JOIN TableA
      ON B.ID = A.ID
;

However, we generally try to structure queries to use LEFT joins instead of RIGHT joins, because a query that mixes them can become very hard to understand and can be very easy to get wrong!

A minor edit to your original query may also help demonstrate why the new query is equivalent:

SELECT
   X.master_key,
   X.vendor_code  
FROM 
   X, Y, X x2 
WHERE
   Y.master_key = X.parent_key  
   AND Y.master_key *= x2.parent_key 
   AND Y.DEFAULT_INITIALS *= x2.INITIALS
   AND Y.VENDOR_ABBREV *= x2.VENDOR_CODE 
   AND Y.project_name = 'TEST'
;

Notice that now all the Y references are on the left side, and the joins are all LEFT joins.