Left Outer and Right Outer Join

Former Member — Thu, 02 Nov 2006 04:51:56 GMT

Hi all,

Can any one tell me the difference between a Left Outer and a Right Outer Join.

Thanks,

Neo.

Re: Left Outer and Right Outer Join

Former Member — Thu, 02 Nov 2006 04:59:19 GMT

The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.

SELECT ...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

<tab> and <dbtab> are subject to the same rules and conditions as in an inner join. The OUTER addition is optional. The tables are linked in the same way as the inner join with the one exception that all lines selected from <tab> are included in the final selection. If <dbtab> does not contain any lines that meet the condition <cond>, the system includes a single line in the selection whose columns from <dbtab> are filled with null values.

In the left outer join, more restrictions apply to the condition <cond> than in the inner join. In addition to the above restrictions:

EQ or = is the only permitted relational operator.

There must be at least one comparison between columns from <tab> and <dbtab>.

The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.

For details and examples of outer join please have a look at below links.

http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm

http://database.ittoolbox.com/documents/popular-q-and-a/inner-and-outer-join-sql-statements-2108;

Re: Left Outer and Right Outer Join

Former Member — Thu, 02 Nov 2006 05:07:10 GMT

LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

The scope of expressions in either the ON clause includes the current tables and any tables in query blocks outer to the current SELECT. The ON clause can reference tables not being joined and does not have to reference either of the tables being joined (though typically it does).

Example:

--match cities to countries

SELECT CITIES.COUNTRY, REGION

FROM Countries

LEFT OUTER JOIN Cities

ON CITY_ID=CITY_ID

WHERE REGION = 'Asia';

-- use the synonymous syntax, RIGHT JOIN, to achieve exactly

-- the same results as in the example above

SELECT COUNTRIES.COUNTRY, REGION

FROM Countries

LEFT JOIN Cities

ON CITY_ID=CITY_ID;

-- Join the EMPLOYEE and DEPARTMENT tables,

-- select the employee number (EMPNO),

-- employee surname (LASTNAME),

-- department number (WORKDEPT in the EMPLOYEE table

-- and DEPTNO in the DEPARTMENT table)

-- and department name (DEPTNAME)

-- of all employees who were born (BIRTHDATE) earlier than 1930

SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME

FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT

ON WORKDEPT = DEPTNO

AND YEAR(BIRTHDATE) < 1930;

-- List every department with the employee number and

-- last name of the manager,

-- including departments without a manager

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME

FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE

ON MGRNO = EMPNO;

Have a look at below link.

http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm

RIGHT OUTER JOIN is one of the JOIN operations that allow you to specify a JOIN clause. It preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table. A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A, with the columns in a different order.

The scope of expressions in the ON clause includes the current tables and any tables in query blocks outer to the current SELECT. The ON clause can reference tables not being joined and does not have to reference either of the tables being joined (though typically it does).

Example:

-- get all countries and corresponding cities, including

-- countries without any cities

SELECT CITY_NAME, CITIES.COUNTRY

FROM CITIES RIGHT OUTER JOIN COUNTRIES

ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;

-- get all countries in Africa and corresponding cities, including

-- countries without any cities

SELECT CITY_NAME, CITIES.COUNTRY

FROM CITIES RIGHT OUTER JOIN COUNTRIES

ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;

WHERE Countries.region = 'frica';

-- use the synonymous syntax, RIGHT JOIN, to achieve exactly

-- the same results as in the example above

SELECT CITY_NAME, CITIES.COUNTRY

FROM CITIES RIGHT JOIN COUNTRIES

ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE

WHERE Countries.region = 'Africa';

-- a TableExpression can be a joinOperation. Therefore

-- you can have multiple join operations in a FROM clause

-- List every employee number and last name

-- with the employee number and last name of their manager

SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME

FROM EMPLOYEE E RIGHT OUTER JOIN

DEPARTMENT RIGHT OUTER JOIN EMPLOYEE M

ON MGRNO = M.EMPNO

ON E.WORKDEPT = DEPTNO;

Also have a look at below link.

http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm

I hope it helps.

Best Regards,

Vibha

*Please mark all the helpful answers

topic Left Outer and Right Outer Join in Application Development and Automation Discussions

Left Outer and Right Outer Join

Re: Left Outer and Right Outer Join

Re: Left Outer and Right Outer Join