The union operation combines two sets of rows into a single set composed of all the rows in either or both of the two original sets provided the two original sets are union compatible, (whereas a join combines two sets of columns into a single set). For union compatibility:
In theory, the union of two sets cannot contain any duplicates. But DB2 provides the provision of retaining or eliminating the duplicates. The UNION verb eliminates duplicates but UNION ALL retains them. Consider the following example:
When tables are joined, rows, which contain matching values in the join predicates, are returned. Sometimes, you may want both matching and no-matching rows returned for the tables that are being joined. This kind of an operation is known as an 'outer join', but DB2 does not support the outer joins explicitly, even though you can achieve the results in a round about way by combining a join and a correlated subquery with a UNION verb. For example, consider the following two tables:
Now, you want to get the supplier number, name, status and city together with the part numbers for all parts supplied by that supplier. Moreover, if a supplier does not supply any parts, you want that information also included in the result by indicating it with a blank part number. You can achieve this by using the following SQL.
The first 4 rows are the result of the natural join of' S' and 'SP' over supplier numbers. The final row. which contains the Information about the supplier who does not supply any parts, is the result of the correlated subquery. The overall result is the outer join of' S' and 'SP' over S#. UNION ALL can always be used in constructing an outer join, since there are no duplicates to be eliminated and UNION ALL will improve performance.
Very often outer joins are required by applications and since DB2 and for that matter most of the relational database management systems, does not support outer joins, it is necessary to learn some workaround like the above example.
Outer join is a top priority requirement for query users and for many application programmers. DB2 has an SQL join function that lets you combine data from two operand tables into a single result table. Releases prior to DB2 version 4 provided an inner join function in which the result table contains only data from rows, which have matching values in the join columns of the operand tables. This function is limited.
If you want a list of all your products and the orders placed by customers during a sales period, including products for which there were no orders, the inner join will not give the desired result. To achieve the desired result with existing function you would have to combine an inner join with a UNION and a NOT EXISTS predicate as we have seen in the above example.
New SQL enhancements to Version 4 let you perform join operations on rows of data in which the values of the join columns do not match. This type of join operation is called an outer join. The result of an outer join can include data from rows that have unmatching values as well as matching values in the join columns from all tables or specific tables.
Unlike inner join, your result table can now include the products that have no orders. This change can improve your productivity and gives you the opportunity to improve performance by using outer join rather than application workarounds.
The new outer join function lets you use the VALUE function or the new COALESCE function to construct a single result column from the non-null values of the join columns of the two-operand tables. New function supports LEFT, RIGHT, and FULL OUTER JOIN and explicit syntax for INNER JOIN consistent with ANSI/ISO SQL entry level standard of 1992.
IBM Mainframe Related Interview Questions
|IBM Lotus Notes Interview Questions||IBM-CICS Interview Questions|
|COBOL Interview Questions||Linux Interview Questions|
|IBM-JCL Interview Questions||IBM Mainframe Interview Questions|
|IBM AIX Interview Questions||IBM WAS Administration Interview Questions|
|IBM Lotus Domino Interview Questions||IBM Integration Bus Interview Questions|
|Mainframe DB2 Interview Questions||Unix Production Support Interview Questions|
Ibm Mainframe Tutorial
Introduction To Software Development
Introduction To Ibm Mainframes
Tso And Ispf
Jes2, ]es3 And Sms
Introduction To Job Control Language (jcl)
The Job Statement
The Exec Statement
The Job And Exec Statements
The Dd Statement
Procedures And Symbolic Parameters
Generation Data Groups (gdg), Compile/link-edit And Run Jcls
Access Method Services (ams)
Additional Vsam Commands
Introduction To Rexx
Overview Of Rexx
Introduction To Cics
Exception Handling In Cics
Developing A Cics Application
Cics Programming Techniques
Basic Mapping Support (bms)
Transient Data Control
Temporary Storage Control
Interval And Task Control
Cics Application Design
Recovery And Restart
System Security And Intersystem Communication
Cics Debugging Facilities And Techniques
Bms Map Definition Macros And Copylib Members
Cics Response And Abend Codes
Data, Information And Information Processing
Introduction To Database Management Systems
Introduction To Relational Database Management Systems
Database Architecture And Data Modeling
Overview Of Db2
Structured Query Language (sql)
Data Security And Access
Db2 Application Development
Qmf And Db2i
Db2 Performance Monitoring, Utilities And Recovery/restart
Overview Of Information Management System (ims)
Introduction To Vs Cobol Ii
Overview Of Application Development In Vs Cobol Ii
Overview Of The Cobol Program
Sorting And Merging Files
Coding Cobol Programs That Run Under Cics. Ims, Db2 And Ispf
Compiling The Program
Link-editing The Program
Executing The Program
Improving Program Performance
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.