Inner join using the JOIN syntax

 

To use the inner join syntax, both of the tables you are joining are listed in the FROM clause, along with the join condition that applies to the tables.

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.

EMPNO	LASTNAME	PROJNO
000250	SMITH	AD3112
000060	STERN	MA2110
000100	SPENSER	OP2010
000020	THOMPSON	PL2100

 

Parent topic:

Inner join