Inner joining tables

If we have tables that have a related field we can join together the tables in a SELECT statement using the JOIN keyword. There are a few different JOIN options depending on how the tables are queried and the results desired. Using the JOIN command returns a simple join where the matching records between the two tables are displayed.

If you remember from a previous lesson we used the WHERE clause to combine fields from two tables. In most database systems, the inner join performs the same function and returns the same results. Using the JOIN statement is often the preferred choice.

You can use the JOIN or INNER JOIN keywords. Below is the basic syntax for an inner join:

SELECT field_name(s)
FROM table1
ON table1.field_name=table2.field_name;

Let’s say we want to display the student’s first name, last name, program they have enrolled in, and what year of the program they are using. We have two tables, student and program, that have the information we need.

The program table contains information just about each program. In the student table we have a field program_id that lets us know which program the student is signed up for. This same field is in the program table. This is the field we will use to relate the two tables together.

SELECT student.last_name, student.first_name,, program.year
FROM student
INNER JOIN program
ON student.program_id = program.program_id;

Only records that have a matching value of the program_id field in both tables will be displayed. If a student record does not have a program_id value that matches a program_id from the program table that student record will not be displayed. Also if a program_id is created in the program table and no student has been assigned it, it will not be displayed.

Lesson Activities

  1. Create an INNER JOIN to show the records of students who have a program assigned from the program table. Include the last_name and first_name fields from the student table and the program’s name and year.
    Click to see code

    SELECT student.last_name, student.first_name, program.program_name, program.year
    FROM student
    INNER JOIN program
    ON student.program_id = program.program_id;
  2. To verify your results view the last_name, first_name, and program_id fields from the student table. Notice that not all the students have a program_id there they do not show on the INNER JOIN.
    Click to see code

    SELECT last_name, first_name, program_id
    FROM student;