Teacher Birgitta Hauser

Let’s Join Together – Joining Tables and Views
 

Abstract:

Accessing data with SQL becomes more and more important. For resolving a single request data located in multiple tables must be filtered, accumulated and/or merged together. In this way we need to focus our attention on consolidating data split over multiple tables. SQL provides different methods for joining tables and views together, such as inner, outer, exception, cross and full join. A table or view even can be joined with itself or with the result of a different select statement.
The result returned from multiple select statements can be merged and even accumulated in a single SQL statement.

 

With Common Table Expressions (CTE) complex queries can be split into smaller pieces, i.e. temporary tables or views that are only valid within the current SQL statement. And the good news is each Common Table Expression can be tested separately.
 

Learning Objectives:
The more normalized a database is, the more tables need to be accessed to get the required data. Instead of reading one table after the other, SQL allows those tables to be joined and merged together by using different join methods for fulfilling the request in a single SQL statement. This session will not only discuss the basics but also demonstrate several tips and tricks for exploiting the join and merge methods.


Audience: Developers and query users who already use or want to use SQL. SQL is not only good for ad hoc queries, but can also be used for generating statistical reports, in RPG or Cobol programs as an alternate for native I/O. The only programming languages that support native I/O are RPG and Cobol. All other languages such as PHP or C++ have to use SQL to get
access to the data located in database objects.

 

Level: Average


Duration: Single Session


Agenda:
SELECT Statement
Joining Tables and Views

  • Inner Join

  • Left/Right Outer Join

  • Left/Right Exception Join

  • Full Join

  • Cross Join

  • Self-Join

NULL Values
Merge Data from different Sub-Selects with UNION, EXCEPT and INTERCEPT
Common Table Expressions

© 2020 by IBMiCompetence.com