SQL JOIN: Inner join

An inner join returns only the rows from each table that have matching values in the join columns.
Any rows that do not have a match between the tables do not appear in the result table.

With an inner join, column values from one row of a table are combined with column values from another row of another (or the same) table to form a single row of data.
SQL examines both tables specified for the join to retrieve data from all the rows that meet the search condition for the join.
There are two ways of specifying an inner join: using the JOIN syntax, and using the WHERE clause.

Suppose you want to retrieve the employee numbers, names, and project numbers for all employees that are responsible for a project.
In other words, you want the EMPNO and LASTNAME columns from the CORPDATA.EMPLOYEE table and the PROJNO column from the CORPDATA.PROJECT table.
Only employees with last names starting with 'S' or later should be considered.
To find this information, you need to join the two tables.

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

Inner join using the WHERE clause

To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause.

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

This query returns the same output as the previous example.

Joining data with the USING clause

You can use the USING clause for a shorthand way of defining join conditions.
The USING clause is equivalent to a join condition where each column from the left table is compared to a column with the same name in the right table.

For example, look at the USING clause in this statement:

           SELECT EMPNO, ACSTDATE
           FROM   CORPDATA.PROJACT INNER JOIN CORPDATA.EMPPROJACT
           USING (PROJNO, ACTNO)
           WHERE  ACSDATE > '1982-12-31';

The syntax in this statement is valid and equivalent to the join condition in the following statement:

           SELECT EMPNO, ACSTDATE
           FROM   CORPDATA.PROJACT INNER JOIN CORPDATA.EMPPROJACT
           ON     CORPDATA.PROJACT.PROJNO = CORPDATA.EMPPROJACT.PROJNO AND
                  CORPDATA.PROJACT.ACTNO  = CORPDATA.EMPPROJACT.ACTNO
           WHERE  ACSTDATE > '1982-12-31';