Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Define INNER/OUTER JOINS

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Define INNER/OUTER JOINS
 Login/Join
 
Member
posted
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
 
Posts: 12 | Location: Omaha, NE | Registered: September 12, 2007Report This Post
<Naak>
posted
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.
 
Report This Post
Virtuoso
posted Hide Post
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.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Guru
posted Hide Post
Gotta love the MATCH... specially OLD-NOR-NEW. Try that with JOIN.

Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Master
posted Hide Post
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)


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
The SET ALL=PASS works in our DB2 environment (on Linux)


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Member
posted Hide Post
Thanks alot to everyone who took the time to respond!

I too like the MATCH command!

Tom
 
Posts: 12 | Location: Omaha, NE | Registered: September 12, 2007Report This Post
Master
posted Hide Post
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
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Guru
posted Hide Post
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. Roll Eyes

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. Music


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Gold member
posted Hide Post
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 !


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
Member
posted Hide Post
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.


WF Production Version - 7.7.03 WIN 7 Solaris Oracle 11g
WF Production Version - 8.0.83 Win 7 Oracle 11g and SQL Server 2008
 
Posts: 7 | Location: Washington, DC | Registered: March 10, 2005Report This Post
Gold member
posted Hide Post
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 ?)


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
Silver Member
posted Hide Post
Try reading the Wikepedia explanation. It is very good.

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


WebFOCUS 7.6.4
Windows
 
Posts: 30 | Registered: May 14, 2008Report This Post
Gold member
posted Hide Post
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.


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Define INNER/OUTER JOINS

Copyright © 1996-2020 Information Builders