Basic Join Part 1
Part of the Fundamentals section of Coddy's SQL journey — lesson 43 of 72.
As of now, we tackled problems of a single table. Now we will examine how to handle multiple tables.
Let's assume we have the following tables:
courses
| course_id | lecturer_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
lecturers
| lecturer_id | name |
|---|---|
| 1 | Jhonas |
| 2 | Malidos |
The task is to present for each course with the corresponding lecturer's name.
There are two ways to achieve this. For each option, we match each row in one table to the other. we check a condition that must be met and if the condition is met we combine the two rows into one. The desired result for both options:
| course_id | lecturer_name |
|---|---|
| 1 | Jhonas |
| 2 | Jhonas |
| 3 | Malidos |
Option 1:
SELECT courses.course_id, lecturers.name AS lecturer_name
FROM courses, lecturers
WHERE courses.lecturer_id = lecturers.lecturer_idHere we write two tables in the FROM keyword, and in the WHERE clause we request that the lecturer_id of both records must be the same. In the SELECT clause we now write table.column so that the database will know where to fetch the column.
Option 2:
SELECT courses.course_id, lecturers.name AS lecturer_name
FROM courses
JOIN lecturers ON courses.lecturer_id = lecturers.lecturer_idInstead of WHERE we use JOIN table ON condition
This type of join is also called an inner join.
Challenge
EasyAvailable tables and columns:
<strong>grades</strong>:<strong>course_id</strong>,<strong>student_id</strong>,<strong>grade</strong><strong>students</strong>:<strong>id</strong>,<strong>name</strong>
Create a query that fetches student_name, course_id, student_id and grade in one table. Order the result by grade in ascending order.
Cheat sheet
To combine data from multiple tables, use either the comma syntax with WHERE or JOIN syntax:
Option 1: Comma syntax with WHERE
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.id = table2.idOption 2: JOIN syntax
SELECT table1.column, table2.column
FROM table1
JOIN table2 ON table1.id = table2.idUse table.column syntax to specify which table a column comes from when selecting from multiple tables. This type of join is called an inner join.
Try it yourself
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Fundamentals
4More Keywords
The IN keywordThe BETWEEN keywordThe LIKE keywordThe AS keywordRecap - Cellphone Models
2Conditions
Conditions BasicsThe AND keywordThe OR keywordThe NOT keywordMultiple Conditions CombinedParenthesisBooleans5Arithmetic Operations
Mathematical OperatorsMathematical ColumnsThe Modulo OperationThe ROUND() Function3Specific Return Format
Null valuesSort Results Part 1Sort Results Part 2Recap - Cyber Security FirmLimit number of recordsRecap - Vehicle Factory6Intro Challenges
Recap - Parliamentary ElectionRecap - Police Criminal ArrestRecap - Bar Beverage ContainerRecap - Engineer new columns