Selecting fields from multiple tables

Up to this point the queries have selected fields from one table. We can add fields from multiple tables and link the record information together based on a relating field.

The basic syntax

To eliminate ambiguous fields we identify which table the field belongs to by using the tablename.fieldname format. We will also use the WHERE condition to show which fields from the two tables are related.

SELECT table1_name.field1_name, table2_name.field_name...
FROM table1_name, table2_name
WHERE table1_name.field_name = table2_name.field_name;

Tables that have related fields

If we want to pull field information about a student from two different fields we will need to know how the two tables relate. Let’s say we want to display the student’s first name, last name, the program they have enrolled in, and what year of the program they are enrolled in. 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.name, program.year
FROM student, program
WHERE student.program_id = program.program_id;

Unions

Another option to select fields from multiple tables is available using the UNION operator.  This list of results does not use a relating field, but rather shows the results of one table, then the results of another table. The UNION operator combines the result of two or more SELECT statements. Usually you use the same field names or fields with the same kind of information in both SELECT statements. Both SELECT statements must have the same number of fields. They must also be similar data types and in the same order.

By default the UNION operator also returns distinct results. If you want to allow duplicate values you can use UNION ALL. Below is an example of the syntax using the UNION operator.

SELECT field_name(s)
FROM table1
UNION
SELECT field_name(s)
FROM table2;

Let’s say we just want a list of all the cities that both the students and faculty live in. We could write the query like this:

SELECT city
FROM student
UNION
SELECT city
FROM faculty

Although chances are that students and faculty will both live in the same cities, the UNION operator will provide a list of distinct values, so each city will only be listed once in results.

Say we want to run a query to return the names of students and faculty, listing their names and city. The fields from both tables have the same names. Here is an example:

SELECT first_name, last_name, city
FROM student
UNION
SELECT first_name, last_name, city
FROM faculty;

Lesson Activities

  1. Select the student’s last name, first name, and program name.
    Click to see code
    SELECT student.last_name, student.first_name, program.program_name
    FROM student, program
    WHERE student.program_id = program.program_id;
  2. Create a list of all the cities both the students and faculty live in. Allow for repeating values of the city names.
    Click to see code
    SELECT city
    FROM student
    UNION ALL
    SELECT city
    FROM faculty;
  3. Create a distinct list of all the cities both the students and faculty live in.
    Click to see code
    SELECT city
    FROM student
    UNION
    SELECT city
    FROM faculty;

Questions