Ian_Hill

CA Tuesday Tip: (CA IDMS) JOIN syntax in SQL

Discussion created by Ian_Hill Employee on Jan 29, 2013
CA IDMS Tuesday Tip by Ian Hill, Principal Support Engineer for January 29, 2013.

Prior to r17.0, the only syntax that could be used to join SQL tables was by specifiying the tables in the FROM clause and identifying the join criteria in the WHERE clause:-

[font=Courier New]SET SESSION CURRENT SCHEMA DEMOEMPL;
SELECT E.EMP_ID, E.EMP_LNAME, D.DEPT_NAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_ID=D.DEPT_ID
AND E.EMP_ID=4001;
*+                                                
*+ EMP_ID  EMP_LNAME             DEPT_NAME        
*+ ------  ---------             ---------        
*+   4001    Thompson                 SALES - NEW CARS 
*+                                                
*+ 1 row processed[font]

As of r17.0, it has been possible to use the more modern "JOIN" syntax:-

[font=Courier New]SET SESSION CURRENT SCHEMA DEMOEMPL;
SELECT E.EMP_ID, E.EMP_LNAME, D.DEPT_NAME
FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.DEPT_ID=D.DEPT_ID
WHERE E.EMP_ID=4001;
*+                                                             
*+ EMP_ID  EMP_LNAME             DEPT_NAME                     
*+ ------  ---------             ---------                     
*+   4001  Thompson              SALES - NEW CARS              
*+                                                             
*+ 1 row processed[font]

This syntax yields the same result but it places the aspects of the JOIN closer to each other and makes it clearer what is going on.

The same syntax can be used when joining network records with a set:-

[font=Courier New]SET SESSION CURRENT SCHEMA EMPNET;
*+ Status = 0        SQLSTATE = 00000                                  
SELECT E.EMP_ID_0415, E.EMP_LAST_NAME_0415, D.DEPT_NAME_0410
FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON "DEPT-EMPLOYEE"
WHERE E.EMP_ID_0415=321;
*+                                                                     
*+ EMP_ID_0415  EMP_LAST_NAME_0415                                     
*+ -----------  ------------------                                     
*+         321  MOON                                                   
*+                                                                     
*+ DEPT_NAME_0410                                                      
*+ --------------                                                      
*+ TEST                                                                
*+                                                                     
*+ 1 row processed[font]

For more information, see the section "Expansion of Joined-table" in Chapter 2 "Identifiers" in the SQL Reference manual.

Outcomes