topic Left outer join in Application Development and Automation Discussions

Left outer join

Former Member — Wed, 28 Nov 2007 18:26:55 GMT

Hi,

Over these years, i had not use the left outer join at all.

Can anyone tell me PRACTICALLY where does it come into picture? I know the concept behind this join. so please do not explian about joins.

thanks

Dany

Re: Left outer join

Former Member — Wed, 28 Nov 2007 19:16:08 GMT

This is of another users, publishing here.

Re: Left outer join

Former Member — Thu, 29 Nov 2007 15:56:28 GMT

Consider the scenerio given below:

Table1 is having list of all incoming email coming to SAP CRM system. THese emails are configured(based on information in subject line) to create sales order in the system.

Table2 is having list of all the emails which are creating sales order and the corresponding sales order number.

Now you want to check the successful and errored emails.

For that you can use

SELECT email salesno from table1 as a left outer join table2 as b

on a.email = b.email.

This will fetch all email records and also it will display NULL in salesno for all those email records which are not creating any sales order.

Hope it helps.

Regards,

Amit

Re: Left outer join

Former Member — Thu, 29 Nov 2007 16:04:25 GMT

I have not used Outer joins since it is more performance issue,Plase take a look at below explanation:

INNER JOIN: Retrieves customers with orders only. For example, you want to determine the amount ordered by each customer and you only want to see those who have ordered something

SELECT Customers., Orders.

FROM Customers INNER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

LEFT OUTER JOIN: Retrieves all customers with or without orders. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN if you switch the side of each table.

SELECT Customers., Orders.

FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

RIGHT OUTER JOIN: Retrieves all orders with or without matching customer records. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers., Orders.

FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

Thanks

Seshu

Re: Left outer join

Former Member — Fri, 30 Nov 2007 14:57:26 GMT

Hi Seshu Maramreddy - Then what is the importance of right outer join?