topic difference between joins in Application Development and Automation Discussions

difference between joins

Former Member — Wed, 21 Nov 2007 16:25:24 GMT

whats the differnce between inner join and left-inner join?

points for clear suggestions.

Message was edited by:

kumar t

Re: difference between joins

naimesh_patel — Wed, 21 Nov 2007 16:30:33 GMT

Inner join will only bring the intersecting values from both tables.

Like VBAK and VBAP , if you use the inner join, system will only bring the intersecting data from both tables. If VBAP doesn't have records related to VBAK, those records will not come from database.

In LEFT outer join, whichever table is on the left side will bring all the records pertaining to where caluse, whereas the other table only brings the data which are intersection in the join.

Regards,

Naimesh Patel

Re: difference between joins

Former Member — Wed, 21 Nov 2007 16:45:52 GMT

In SAP there is no concept of Left Inner Join.

We only have INNER JOIN and OUTER JOIN

See the link below for more details

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

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

reward points if useful.`

Regards,

saket

Re: difference between joins

balbino_soaresferreirafil — Wed, 21 Nov 2007 17:19:08 GMT

Hi,

Using Joins

We can select data from two tables with the JOIN keyword, like this:

Example INNER JOIN

Syntax

SELECT field1, field2, field3

FROM first_table

INNER JOIN second_table

ON first_table.keyfield = second_table.foreign_keyfield

Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product

FROM Employees

INNER JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

Result

Name Product

Hansen, Ola Printer

Svendson, Stephen Table

Svendson, Stephen Chair

Example LEFT JOIN

Syntax

SELECT field1, field2, field3

FROM first_table

LEFT JOIN second_table

ON first_table.keyfield = second_table.foreign_keyfield

List all employees, and their orders - if any.

SELECT Employees.Name, Orders.Product

FROM Employees

LEFT JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

Result

Name Product

Hansen, Ola Printer

Svendson, Tove

Svendson, Stephen Table

Svendson, Stephen Chair

Pettersen, Kari

Example RIGHT JOIN

Syntax

SELECT field1, field2, field3

FROM first_table

RIGHT JOIN second_table

ON first_table.keyfield = second_table.foreign_keyfield

List all orders, and who has ordered - if any.

SELECT Employees.Name, Orders.Product

FROM Employees

RIGHT JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.

Result

Name Product

Hansen, Ola Printer

Svendson, Stephen Table

Svendson, Stephen Chair

Example

Who ordered a printer?

SELECT Employees.Name

FROM Employees

INNER JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

WHERE Orders.Product = 'Printer'

Result

Name

Hansen, Ola

Re: difference between joins

Former Member — Wed, 21 Nov 2007 18:03:45 GMT

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: difference between joins

Former Member — Fri, 28 Dec 2007 06:17:41 GMT

thanks friends in helping me out.