SQL JOINS

DB2 SQL JOINS : INNER JOIN


INNER Join returns records which are common in all tables based on given join condition. There are two ways to write INNER Join:[SAMPLE DATA]

  1. With JOIN keyword
    SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf= b.keyf
    — From SQLTEST1 — — From SQLTEST2 —
    FILE_KEY FILE_DOB FILE_KEY FILE_NAME
    01 10/10/99  01 SUMIT
    02 10/10/00 02 AMIT

    You can JOIN more than two tables:

    SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf = b.keyf JOIN SQLTEST3 c ON b.keyf =c.keyf
    — From SQLTEST1 — — From SQLTEST2 — — From SQLTEST3 —
    FILE_KEY FILE_DOB FILE_KEY FILE_NAME FILE_KEY FILE_LAST
    01 10/10/99 01 SUMIT 01 Goyal
  2. Without JOIN keyword
    SELECT * FROM SQLTEST1 AS a , SQLTEST2 AS b WHERE a.keyf= b.keyf

    This is same as:

    SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf= b.keyf
    1. JOIN keyword is replaced with “,”
    2. ON keyword is replaced with WHERE clause.
    3. When JOIN keyword is used ON must be used instead of WHERE to specify the join condition.

IBM i developer.

View Comments
There are currently no comments.