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

updated with my changes to query.. but still i am not able to get the desired results.. — Sudhakar Kummarasetty, Dec 19 '13 at 06:56
left join and right join do exactly the same, it's just a logical point of view. For easier logic pick left or right but always to the same. Personaly i only use left join. And don't use from x,y. Use all possible join as shown here below :) — Mathese F, Dec 19 '13 at 07:10

ErikE · Answer 1 · 2020-07-17T01:47:54.330

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.

Thank you very very much , it gave some better idea.. actually I am java programmer , stuck with .. sql and it is like fresher only — Sudhakar Kummarasetty, Dec 19 '13 at 07:19

Saharsh Shah · Accepted Answer · 2013-12-19T07:25:22.693

3

Try this:

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'

Your Second answer

SELECT vnd.master_key, vnd.vendor_code  
FROM vnd
INNER JOIN vnm ON vnm.master_key = vnd.parent_key AND vnm.inactive = 0 
LEFT OUTER JOIN vnd vn2 ON vnm.master_key = vn2.parent_key AND vn2.INITIALS = vnm.DEFAULT_INITIALS AND vn2.VENDOR_CODE = vnm.VENDOR_ABBREV
WHERE 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   +  ' ')

edited Dec 19 '13 at 07:25

answered Dec 19 '13 at 07:00

Saharsh Shah

28,687
8
48
83

Thank you very very much Saharsh .. could you tell how you came to this querry – Sudhakar Kummarasetty Dec 19 '13 at 07:10
I see where condition is changed and you have added inner join and removed completely the RIGHT OUTER JOIN ... – Sudhakar Kummarasetty Dec 19 '13 at 07:10
@user2045810 You're most welcome. Check your query you have joined table Y and X2 table with 3 conditions and all conditions says that return records from Y table with matching records of X2 table. So I had used LEFT OUTER JOIN to join Y and X2 table. – Saharsh Shah Dec 19 '13 at 07:13
@user2045810 Check your first where condition which says that inner join between X & Y table so I had used INNER JOIN to join both tables – Saharsh Shah Dec 19 '13 at 07:14
Why did u add "AND vnm.inactive = 0 " in the inner join – Sudhakar Kummarasetty Dec 19 '13 at 09:28

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

2 Answers2