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, 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.

• 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.

• 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.

 

Parent topic:

Joining data from more than one table