ImplementationINNER JOIN

Used to exemplify equi-join

An equi-join is one type of INNER JOIN that can be applied using SQL.

Equi-joins will be used with SELECT statements to select data from more than one table. If the conditions specified in the INNER JOIN are met, then data from both tables is usually returned.

The word equi is used in reference to the = operator.

INNER JOINS can support use of < and > operators but it is only necessary to understand use of the = operator at National 5 level.

We already have a Pupil table, with the updated version shown below:

The database also has a table holding information on members of the school orchestra. This table is called 'Orchestra' and contains the following details:

SELECT Orchestra.Group, Pupil.First Name, Pupil.Surname

FROM Orchestra

INNER JOIN Pupil ON Orchestra.PupilID = Pupil.PupilID;

Would return:

Pupil ID is the primary key in the Pupil table. It appears as a foreign key in the Orchestra table. The equi-join is used to match the values held in both tables, so that results are only returned where a match exists.

Using a WHERE clause can further refine the results. In this example, only pupils who are part of the brass group are returned.

SELECT Orchestra.Group, Pupil.First Name, Pupil.Surname

FROM Orchestra

INNER JOIN Pupil ON Orchestra.PupilID = Pupil.PupilID 

WHERE Orchestra.Group = ‘Brass’;