SQL400 for Beginners Part 4

Hi Guys,

I am back with Joins in SQL400. I hope till now you would have basic understanding of SQl400. After this blog, you would be able to know more on Joins.

SQL JOIN Statements

There are five types of SQL Joins

1.Inner Join

2.Left Outer Join

3. Exception Join

4. Cross Join

5. Multiple Join types in one Statement

SQL INNER JOIN

The SQL INNER JOIN clause tells the database to only return rows where there is a match found between table1 and table2.

An INNER JOIN is most often (but not always) created between the primary key column of one table and the foreign key column of another table.

The below example will display INNER JOIN between two different files SCHMST and SCHPERI1 :

SELECT STNAME, STCLASS, STFNAME ,STCITY  FROM SCHMST INNER JOIN  SCHPERI1  ON  STENROLLN = STENROLL1 WHERE STENROLLN >='SE001'

The above statement will fetch various columns from file SCHMST where the value of field STENTOLLN in SCHMST is equal to field STENROLL1 in file SCHPERI1 and STENTOLLN is greater than ‘SE001’

SQL LEFT OUTER JOIN

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

Syntax and Example:

SELECT COLUMN1F1, COLUMN2F1 FROM FILE1

    LEFT OUTER JOIN FILE2 ON COLUMN1F1 = COLUMN1F2 WHERE COLUMN2F1 > 'S'

The next example will display LEFT OUTER JOIN between two different files SCHMST and SCHPERI1 :

SELECT STNAME, STCLASS, STFNAME ,STCITY FROM SCHMST LEFT OUTER JOIN  SCHPERI1 ON  STENROLLN = STENROLL1 WHERE STENROLLN >='SE001'

The above query will display all rows of file SCHMST for which field STENROLLN is greater than or Equal to ‘SE001’.

Also, data from only those rows of file SCHPERI1 will be displayed which have field STENROLL1 equal to STENROLLN in file SCHMST.

A Left Outer Join returns values for all of the rows from the first table (the table on the left) and the values from the second table for the rows that match. Any rows that do not have a match in the second table will return the null value for all columns from the second table. To avoid this situation IFNULL can be used with 0 or balanks/character string depending on data type-: 

Select Customer.CustId, 
Customer.Name As 'Customer_Name', 
IfNull( SalesRep.Name, 'Not assigned' ) As 'Sales_Rep_Name' 
From Custome Left Outer Join 
SalesRep On Customer.CustId = SalesRep.CustId 

However, IFNULL does not follow ANCI standard 

It is recommended use COALESCE instead: 
SELECT coalesce(emple, 0) wrk,deptno 
FROM department LEFT OUTER JOIN employee 
ON deptno=wrkdpt 

SQL EXCEPTION JOIN

A left exception join returns only the rows from the first table that do NOT have a match in the second table.

Syntax and Example –

     SELECT COLUMN1F1, COLUMN2F1 FROM FILE1

     EXCEPTION JOIN FILE2 ON COLUMN1F1 = COLUMN1F2 WHERE COLUMN2F1 > 'S'

The next example will display EXCEPTION JOIN between two different files SCHMST and SCHPERI1 :

SELECT STNAME, STCLASS, STFNAME ,STCITY  FROM SCHMST EXCEPTION JOIN  SCHPERI1 ON  STENROLLN = STENROLL1 WHERE STENROLLN >='SE001'

The above query  will display rows of file SCHMST which have value of field STENTOLLN greater than or equal to ‘SE001’ AND does not match the value of field STENROLL1 in file SCHPERI1

SQL CROSS JOIN

SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table, which also means CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

SQL CROSS JOIN syntax:

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

OR

SELECT * FROM [TABLE 1], [TABLE 2]

The next example will display CROSS JOIN between two different files SCHMST and SCHPERI1 :

SELECT * FROM SCHMST CROSS JOIN SCHPERI1

Each record in SCHMST is combined with all records in file SCHPERI1 and the output is displayed on screen.

Multiple join types in one statement

There are times when more than two tables need to be joined to produce the desired result.

Example - If you want to return all the employees, their department name, and the project they are responsible for, if any, the EMPLOYEE table, DEPARTMENT table, and PROJECT table would all need to be joined to get the information.

The following example shows the query:

SELECT EMPNO, LASTNAME, DEPTNAME, PROJNO FROM EMPLOYEE INNER JOIN DEPARTMENT ON WORKDEPT = DEPTNO LEFT OUTER JOIN PROJECT ON EMPNO = RESPEMP WHERE LASTNAME > 'S'

The next example will display MULTIPLE JOIN between two different files SCHMST and SCHPERI1 :

SELECT A.STNAME, A.STCLASS,STMARK, STFNAME, STCITY  FROM SCHMST A  INNER JOIN SCHPERI1 ON A.STENROLLN = STENROLL1 LEFT OUTER JOIN SCHMST1 ON STENROLLN = STENROLL1 WHERE STENROLLN >= 'SE001'

The above query uses a prefix ‘A’ for identifying the fields/columns of file SCHMST. Other parts of the query are already explained.

SQL UNION and UNION ALL

Using the UNION keyword to combine subselects

The SQL UNION is used to combine the results of two or more SELECT SQL statements into a single result. All the statements concatenated with UNION must have the same structure. This means that they need to have the same number of columns, and corresponding columns must have the same or compatible data types (implicitly convertible to the same type or explicitly converted to the same type). The columns in each SELECT statement must be in exactly the same order too.

This is how a simple UNION statement looks like:

SELECT Column1, Column2 FROM Table1
UNION
SELECT Column1, Column2 FROM Table2

The next example will display the use of UNION Keyword between two different files SCHMST and SCHMST1 :

SELECT STNAME FROM SCHMST

UNION                                        

SELECT STNAME FROM SCHMST1 WHERE STMARK >100

The above query will fetch only those records from both files which satisfy both the above conditions.

Using the UNION ALL keyword to combine subselects

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

The next example will display the use of UNION ALL Keyword between two different files SCHMST and SCHMST1 :

Using the UNION ALL keyword to combine subselects

SELECT STNAME FROM SCHMST                   

UNION  ALL                                     

SELECT STNAME FROM SCHMST1 WHERE STMARK >100

As mentioned in the last slide, the command ‘UNION ALL’ will allow selecting duplicate records as compared to only distinct records by ‘UNION’ command

SQL Sub-Queries

• Sub-queries in SELECT statements
• Correlation
• Sub-queries and search conditions
• How sub-queries are used
• Using sub-queries with UPDATE and DELETE

Subqueries in SELECT statements

It is possible to embed a SQL statement within another. When this is done on the WHERE etc statements, we have a subquery construct.

The syntax is as follows:

SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name3"
FROM "table_name2"
WHERE [Condition]) 

SQL Sub-Queriesou

The next two examples will display an example of Sub-Queries between two different files SCHMST and SCHMST1 :

Subqueries in SELECT statements

SELECT * FROM SCHMST WHERE STNAME IN (SELECT  STNAME FROM  

SCHMST1 WHERE STMARK>=300)

The above example shows the sub-queries or Nesting statement.

Firstly, the column STNAME is selected from file SCHMST1 for which field STMARK is greater than or equal to 300.

Thereafter, only those records will be selected from file SCHMST for which field STNAME exists in the output of the earlier query. This will be displayed on screen

Using subqueries with UPDATE and DELETE

UPDATE SCHMST SET STENROLLN=‘XXXXX'  WHERE     

 STNAME IN (SELECT  STNAME FROM                

 SCHMST1 WHERE STMARK>=300)

The above query firstly selects STNAME for only those records fyroguum file SCHMST1 where column STMARK is greater than or equal to 300.

Thereafter, rows are selected from file SCHMST where value of field STNAME exists in the output of the earlier query.

For these rows, value of column STENROLLN is updated as ‘XXXXX’

I hope by now all you guys would be having good knowledge of SQL400.....


Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

THANK  YOU