When working with tables in SQL, there may be some situations when you need to query three or more tables. You can join three tables by first using a join statement to join two tables to create a temporary joined table. Then use a second join statement to join the third table.
Steps
Type SELECT followed by the column names you want to query.
Type the column name from each of the three you want to query. Separate each column name with a comma. In this example, we will be querying from three tables named “Students”, “Schools”, “Details”
- For example SELECT student_id, student_name, school_id, school, grade
Type FROM followed by the first table name.
This can go on a separate line or immediately after the first statement. In our example we would type FROM Students.
Type a join statement followed by the name of the second table.
There are four types of join statements you can use to join two tables. They are as follows:
- Type JOIN to do an Inner Join. This returns records that have matching values in both tables. For example FROM Students JOIN Details.
- Type LEFT JOIN to do an Outer Left Join. This returns all records from the left table and matching values from the right table. For example FROM Students LEFT JOIN Details.
- Type RIGHT JOIN to do a Outer Right Join. This returns all records from the right table and matching values from the left table. For example FROM Students RIGHT JOIN Details.
- Type FULL JOIN to do a Full Outer Join. This returns all records from both tables. For example FROM Students FULL JOIN Details.
Type an “ON” statement for the tables and columns are being joined.
The syntax for this statement is “ON table_1.primary_key = table_2.foreign_key”. “Table_1” is the name of the first table you are joining, and “primary_key” is primary column name in the first table. “Table_2” is the name of the second table, and “foreign_key” is a column name from the second table that matches the primary column from the first table.
- In our example, “Students” is the first table and “student_id” is the primary key from the Students table, which is also in the Details table. So we would type ON Students.student_id = Details.student_id. This joins the Students table with the Details table using “student_id” as the primary key.
- Alternatively, if the student_name column is in the “Details” table, you can display the student_name column in place of the student_id field by typing ON Students.student_id = Details.student_name.
Type a join statement followed by the name of the third table.
This can be on a separate line or immediately after the “ON” statement that joined the first two tables. You can use any one of the four join statements.
- In our example, we would type JOIN Schools.
Type an “ON” statement that indicates which tables and columns will be joined.
The syntax for the third join is “ON table_3.primary_key = table_1.foreign_key”. “Table_1”. “Table_3 is the name of the third table. This adds table three to the final join using the primary column name from the third table and the foreign key from the first table. In our example, this we would type ON Schools.student_id = Students.student_id. The entire join statement should look something like this: