Teacher Birgitta Hauser
Let’s Join Together – Joining Tables and Views
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.
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.
Duration: Single Session
Joining Tables and Views
Left/Right Outer Join
Left/Right Exception Join
Merge Data from different Sub-Selects with UNION, EXCEPT and INTERCEPT
Common Table Expressions