Joins on Multiple Tables

Sql tutorial · PySpark.in

Joins on Multiple Tables

A query can join more than two tables.

This is useful when the required data is spread across several related tables.

For example:

course stores course details
student_course stores enrollment details
student stores student details

Example

Fetch the names of students who enrolled in courses taught by the instructor Arun (instructor_id = 109).

```

sql

DROP TABLE IF EXISTS student_course;

DROP TABLE IF EXISTS student;

DROP TABLE IF EXISTS course;

-- Create course table

CREATE TABLE course (

course_id INTEGER PRIMARY KEY,

course_name TEXT,

instructor_id INTEGER

);

-- Create student table

CREATE TABLE student (

student_id INTEGER PRIMARY KEY,

full_name TEXT

);

-- Create student_course table

CREATE TABLE student_course (

enrollment_id INTEGER PRIMARY KEY,

student_id INTEGER,

course_id INTEGER

);

-- Insert courses

INSERT INTO course (course_id, course_name, instructor_id) VALUES

(15, 'Machine Learning', 109),

(16, 'Cyber Security', 109),

(17, 'SQL Basics', 110);

-- Insert students

INSERT INTO student (student_id, full_name) VALUES

(1, 'Amit Sharma'),

(2, 'Neha Verma'),

(3, 'Rohit Kumar'),

(4, 'Priya Singh');

-- Insert enrollments

INSERT INTO student_course (enrollment_id, student_id, course_id) VALUES

(101, 1, 15),

(102, 2, 15),

(103, 3, 16),

(104, 4, 17);

-- Fetch students enrolled in courses taught by instructor 109

SELECT course.course_name,

student.full_name

FROM course

INNER JOIN student_course

ON course.course_id = student_course.course_id

INNER JOIN student

ON student_course.student_id = student.student_id

WHERE course.instructor_id = 109;

```

How It Works

course joins student_course using course_id
the result joins student using student_id
finally, the WHERE clause filters only courses taught by instructor 109

Best Practices

Use aliases for shorter and cleaner queries
Always qualify columns when multiple tables contain similar column names

Example with aliases:

```

sql

SELECT c.course_name,
s.full_name
FROM course AS c
INNER JOIN student_course AS sc
ON c.course_id = sc.course_id
INNER JOIN student AS s
ON sc.student_id = s.student_id
WHERE c.instructor_id = 109;

```

Using Joins with Other Clauses

After joining tables, you can use clauses such as:

WHERE
ORDER BY
GROUP BY
HAVING
LIMIT

just like in normal queries.

Example

Get the name of the student who scored the highest marks in the Machine Learning course.

```

sql

DROP TABLE IF EXISTS student_course;

DROP TABLE IF EXISTS student;

DROP TABLE IF EXISTS course;

-- Create course table

CREATE TABLE course (

course_id INTEGER PRIMARY KEY,

course_name TEXT

);

-- Create student table

CREATE TABLE student (

student_id INTEGER PRIMARY KEY,

full_name TEXT

);

-- Create student_course table

CREATE TABLE student_course (

enrollment_id INTEGER PRIMARY KEY,

student_id INTEGER,

course_id INTEGER,

score INTEGER

);

-- Insert courses

INSERT INTO course (course_id, course_name) VALUES

(15, 'Machine Learning'),

(16, 'Cyber Security'),

(17, 'SQL Basics');

-- Insert students

INSERT INTO student (student_id, full_name) VALUES

(1, 'Amit Sharma'),

(2, 'Neha Verma'),

(3, 'Rohit Kumar'),

(4, 'Priya Singh');

-- Insert enrollments with scores

INSERT INTO student_course (enrollment_id, student_id, course_id, score) VALUES

(101, 1, 15, 88),

(102, 2, 15, 95),

(103, 3, 15, 91),

(104, 4, 16, 84);

-- Get the student who scored highest in Machine Learning

SELECT student.full_name

FROM course

INNER JOIN student_course

ON course.course_id = student_course.course_id

INNER JOIN student

ON student_course.student_id = student.student_id

WHERE course.course_name = 'Machine Learning'

ORDER BY student_course.score DESC

LIMIT 1;

```

How It Works

the tables are joined first
WHERE filters only the Machine Learning course
ORDER BY sorts scores from highest to lowest
LIMIT 1 returns only the top student

Using Joins with Aggregations

Aggregate functions such as:

SUM()
AVG()
COUNT()
MAX()
MIN()

can also be used on joined tables.

Example

Get the highest score in each course.

```

sql

DROP TABLE IF EXISTS student_course;

DROP TABLE IF EXISTS course;

-- Create course table

CREATE TABLE course (

course_id INTEGER PRIMARY KEY,

course_name TEXT

);

-- Create student_course table

CREATE TABLE student_course (

enrollment_id INTEGER PRIMARY KEY,

student_id INTEGER,

course_id INTEGER,

score INTEGER

);

-- Insert courses

INSERT INTO course (course_id, course_name) VALUES

(15, 'Machine Learning'),

(16, 'Cyber Security'),

(17, 'SQL Basics'),

(18, 'Python Basics');

-- Insert enrollments with scores

INSERT INTO student_course (enrollment_id, student_id, course_id, score) VALUES

(101, 1, 15, 88),

(102, 2, 15, 95),

(103, 3, 16, 91),

(104, 4, 16, 84),

(105, 5, 17, 79);

-- Get the highest score in each course

SELECT course.course_name,

MAX(student_course.score) AS highest_score

FROM course

LEFT JOIN student_course

ON course.course_id = student_course.course_id

GROUP BY course.course_id, course.course_name;

```

Joins on Multiple Tables