Joins Part 1 in SQL

 JOINS

So far, we have learnt to analyse the data that is present in a single table. But in the real-world scenarios, often, the data is distributed in multiple tables. To fetch meaningful insights, we have to bring the data together by combining the tables.

We use JOIN clause to combine rows from two or more tables, based on a related column between them. There are various types of joins, namely Natural join, Inner Join, Full Join, Cross Join, Left join, Right join.

Let’s learn about them in detail using the following database.

Database Here, the database stores the data of students, courses, course reviews, instructors, etc., of an e-learning platform.

Refer the tables in the code playground for a better understanding of the database.

Natural JOIN

NATURAL JOIN

combines the tables based on the common columns.

Syntax

1

2

3

SELECT *

FROM table1

NATURAL JOIN table2;

SQL

Example

1. Fetch the details of courses that are being taught by "Alex".

Solving this problem involves querying on data stored in two tables, i.e.,

course

&

instructor

. Both the tables have common column

instructor_id

. Hence, we use Natural Join.

1

2

3

4

5

SELECT course.name,

instructor.full_name

FROM course

NATURAL JOIN instructor

WHERE instructor.full_name = "Alex";

SQL

Output

name	full_name
Cyber Security	Alex

Try it Yourself!

Question 1: Get the details of the instructor who is teaching "Cyber Security".

Expected Output:

full_name	gender
Alex	M

Question 2: Get student full name and their scores in "Machine Learning" (course with id=11).

Expected Output:

full_name	score
Varun	80
Sandhya	90

INNER JOIN

INNER JOIN

combines rows from both the tables if they meet a specified condition.

Syntax

1

2

3

4

SELECT *

FROM table1

INNER JOIN table2

ON table1.c1 = table2.c2;

SQL

Note

We can use any comparison operator in the condition.

Example

Get the reviews of course “Cyber Security” (course with id=15)

1

2

3

4

5

6

7

SELECT student.full_name,

review.content,

review.created_at

FROM student

INNER JOIN review

ON student.id = review.student_id

WHERE review.course_id = 15;

SQL

Output

full_name	content	created_at
Ajay	Good explanation	2021-01-19
Ajay	Cyber Security is awesome	2021-01-20

Try it Yourself!

Question 1: Get the details of students who enrolled for "Machine Learning" (course with id=11).

Expected Output:

full_name	age	gender
Varun	16	M
Sandhya	19	F

Question 2: Get the reviews given by "Varun" (student with id = 1)

Expected Output:

course_id	content	created_at
11	Great course	2021-01-19

LEFT JOIN

In

LEFT JOIN

, for each row in the left table, matched rows from the right table are combined. If there is no match, NULL values are assigned to the right half of the rows in the temporary table.

Syntax

1

2

3

4

SELECT *

FROM table1

LEFT JOIN table2

ON table1.c1 = tabl2.c2;

SQL

Example

Fetch the full_name of students who have not enrolled for any course

1

2

3

4

5

SELECT student.full_name

FROM student

LEFT JOIN student_course

ON student.id = student_course.student_id

WHERE student_course.id IS NULL;

SQL

Output

full_name
Afrin

Try it Yourself!

Question 1: Get the course details that doesn't have any students.

Expected Output:

name
Linux

Question 2: Get the instructors details who is not assigned for any course.

Expected Output:

full_name	gender
Bentlee	M