Hello all.

This is the type of question that a "newbie" might ask, but I'm going to ask it anyway!

Can someone provide me with a clear, simple definition (maybe even an example?) of what an INNER JOIN and an OUTER JOIN is? I have been using FOCUS (both mainframe and WebFOCUS), and JOINS for many, many years. Now I see the terms "inner" and "outer" relating to JOINS, and I'm just not sure what they are or how they manifest themselves in the code!

Any and all help would be greatly appreciated!

Thank you!!

Tom

Records that are not in both files, the join is called an inner join.
Report displays all matching records, plus all records from the host file, the join is called a left outer join.
Report displays all matching records, plus all records from the cross-referenced file, the join is called a right outer join.

Not quite.

Only display records that ARE in both files is an inner join.
Display all records from left (HOST) file plus matching records from right (cross reference) is left outer join.
There is no such thing as a right outer join in FOCUS, so you don't need to worry about that one. To get the same effect, you would switch the host and cross-reference files in the join and do a left outer join.
There is also MATCH logic that you can read up on, in case that isn't confusing enough.

Gotta love the MATCH... specially OLD-NOR-NEW. Try that with JOIN.

Sayed

Tom, The problem is that some databases (DB2 for example) will not recognise the old coding of SET ALL = PASS (Outer join) OR OFF (Inner Join)

The SET ALL=PASS works in our DB2 environment (on Linux)

There should be a mapping of Match and Join.

I find the "Toyota Rings" great for a visual representation of Join or Match.

The documentation is here

Thanks alot to everyone who took the time to respond!

I too like the MATCH command!

Tom

Interesting Darin, maybe I'm having a senior moment and I haven't used DB2 on Linux, but I was told DB2 ignored the SET ALL command.

Pat,

Let's not talk about senior moments. I am quickly coming up on one of those milestones that everyone in the office love to capitalize on.

The number doesn't bother me it's all the unnecessary attention.

So instead of "senior moment" let's call it "sometimers disease" because sometimes I remember and sometimes I don't.

Part 1)
This is how I see “OUTER JOINS” :
An "Inner Join" shows only ‘parent’ records that have ‘children’.
This does NOT show parents without children, nor does it show children without parents.
Lots of data records can ‘get lost’ with this method !

An "Outer Join" shows ALL of the records in one table REGARDLESS of whether there is a match in the other.
Use these to help find that ‘lost data’.

The "Outer Joins" are broken into two types :
-------------------------------------------------------
A "Left Outer Join" shows ALL of the ‘parent’ records regardless of whether there is a ‘child’ record.
A "Right Outer Join" shows ALL of the ‘child’ records regardless of whether it has a ‘parent’ record.

Even if you switch the HOST and CROSS-REFERENCED files, I think this puts it in perspective ?

Have fun !

I use SET ALL=ON with DB2 I have always been told that SET ALL=PASS is not supported by the DB2 adapter.
I also use
SQL DB2 SET SQLJOIN OUTER ON to tell DB2 to do the join if it can to prevent a table dump and Focus handling the join.

Part 2)

Some examples:
--------------
A) "Left Outer Join" : Parents without children
------------------------------------------------
This is not really a database error, but may be a business problem, depending on the business needs.
Some accounts may have no related transactions (haven't bought anything).
------------------------------------------------
Use a "Left Outer Join" to find all 'parents' regardless of whether they have 'children'.
If a customer record contains a 'null' ('missing') key to a 'child' record, send them a catalog ?
===============================================================================

B) "Right Outer Join" : Children without parents
------------------------------------------------
This is a DBMS problem, if a transaction record does not have a 'parent' account !
It is also a problem for the business : Who pays for the transaction, or worse, who is responsible for the child ?
------------------------------------------------

Use a "Right Outer Join" to find all 'children' regardless of whether they have 'parents'.
If a client has no caseworker, assign one temporarily to satisfy this requirement, and use for an exception report.
===============================================================================

Fixing these problems is the subject of another article, but these are the tools that you would use to normalize the data, to remove these data anomalies.

(i.e.: Combine code for LEFT and RIGHT JOINS with MISSING data in the related tables to find only those records that cause the problem.)

The MATCH command seems to be the tool of choice, to fix whatever problem you may have ?)

Try reading the Wikepedia explanation. It is very good.

http://en.wikipedia.org/wiki/Join_(SQL)

The Wiki definition leaves a little to be desired, for example, LEFT and RIGHT are both OUTER joins.
So there are really two types of joins : INNER and OUTER.
There is only one kind of INNER join.
There are two kinds of OUTER joins, RIGHT and LEFT, but only LEFT is supported by (Web)FOCUS.
(Actually, there is also a FULL OUTER join that shows ALL records in both tables, but this is rarely used.)
(See Venn Diagrams in Waz's "Toyota Rings" previous post)
Although RIGHT OUTER joins are not supported in WebFOCUS, simply switching, or pivoting, the HOST and X-REF files accomplishes the same thing.

This may hurt your head because the child is now the parent, and the parent is now the child, but it CAN be done. It's simply "negative logic" which simply reverses the roles of the tables, but accomplishes the same thing.)

An OUTER join simply shows all of the records in one table REGARDLESS of whether there is a match in the related table.

LEFT and RIGHT simply describe which one is "in control" of the relationship.
This can be pivoted by exchanging the roles to make a RIGHT OUTER join look like a LEFT OUTER join.