Inner join using the JOIN syntax

The join condition is specified after the ON keyword and determines how the two tables are to be compared to each other to produce the join result. The condition can be any comparison operator; it does not need to be the equal operator. Multiple join conditions can be specified in the ON clause separated by the AND keyword. Any additional conditions that do not relate to the actual join are specified in either the WHERE clause or as part of the actual join in the ON clause.

  SELECT EMPNO, LASTNAME, PROJNO
    FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.PROJECT
          ON EMPNO = RESPEMP
    WHERE LASTNAME > 'S'

In this example, the join is done on the two tables using the EMPNO and RESPEMP columns from the tables. Since only employees that have last names starting with at least 'S' are to be returned, this additional condition is provided in the WHERE clause.

This query returns the following output.