Structured Query Reporter (SQR)

A JOIN is a means for combining fields from two tables by using values common to each.

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record?even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

(In this case left and right refer to the two sides of the JOIN keyword.)

No implicit join-notation for outer joins exists in standard SQL.

Left outer join

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result?but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.

For example, this allows us to find an employee's department, but still shows the employee(s) even when they have not been assigned to a department (contrary to the inner-join example above, where unassigned employees are excluded from the result).

Example of a left outer join:

SELECT *

FROM employee LEFT OUTER JOIN department

ON employee.DepartmentID = department.DepartmentID

Right outer joins

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.

A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).

For example, this allows us to find each employee and his or her department, but still show departments that have no employees.

Example right outer join:

SELECT *

FROM employee RIGHT OUTER JOIN department

ON employee.DepartmentID = department.DepartmentID

Inner Join

An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate. Actual SQL implementations normally use other approaches like a Hash join or a Sort-merge join where possible, since computing the Cartesian product is very inefficient.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation".

The "explicit join notation" uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:

SELECT *

FROM employee

INNER JOIN department

ON employee.DepartmentID = department.DepartmentID

BI - Business Inerlink - This is peoplesoft object that behave like DLL. If you have to modify something on other application, like some of directory that controls single sign on Business Interliks are used.

CI: Component Interface - This is used to validate any entries in peoplesoft system. say some third party system sent one inbound file and based on the data in inbound file you want to insert row in peoplesoft, you also want the current validation at component (say xyz) to take place. Then create a component interface with xyz component. read the inbound and process it through newly build CI.

IB - Inegraion Broker - This is integration point of peoplesoft with all other peoplesoft and thirdparty application that uses Messaging.