Joins Part 3

 JOINS Cont'd

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

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

RIGHT JOIN

RIGHT JOIN or RIGHT OUTER JOIN is vice versa of LEFT JOIN. I.e., in

RIGHT JOIN

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

Syntax

1

2

3

4

SELECT *

FROM table1

RIGHT JOIN table2

ON table1.c1 = table2.c2;

SQL

Which is similar to

1

2

3

4

SELECT *

FROM table2

LEFT JOIN table1

ON table1.c1 = table2.c2;

SQL

Example

Following query performs RIGHT JOIN on course and instructor tables

1

2

3

4

5

SELECT course.name,

instructor.full_name

FROM course

RIGHT JOIN instructor

ON course.instructor_id = instructor.instructor_id;

SQL

Note

Right Join is not supported in some dbms(SQLite).

FULL JOIN

FULL JOIN or FULL OUTER JOIN is the result of both RIGHT JOIN and LEFT JOIN

Syntax

1

2

3

4

SELECT *

FROM table1

FULL JOIN table2

ON c1 = c2;

SQL

Example

Following query performs FULL JOIN ON course and instructor tables

1

2

3

4

5

SELECT course.name,

instructor.full_name

FROM course

FULL JOIN instructor

ON course.instructor_id = instructor.instructor_id;

SQL

Note

FULL JOIN is not supported in some dbms(SQLite).

CROSS JOIN

In CROSS JOIN, each row from the first table is combined with all rows in the second table. Cross Join is also called as CARTESIAN JOIN

Syntax

1

2

3

SELECT *

FROM table1

CROSS JOIN table2;

SQL

Example

Following query performs CROSS JOIN on course and instructor tables

1

2

3

4

SELECT course.name AS course_name,

instructor.full_name AS instructor_name

FROM course

CROSS JOIN instructor;

SQL

Output

course_name	instructor_name
Machine Learning	Alex
Machine Learning	Arun
Machine Learning	Bentlee
Cyber Security	Alex
...	...

SELF JOIN

So far, we have learnt to combine different tables. We can also combine a table with itself. This kind of join is called SELF-JOIN.

Syntax

1

2

3

4

5

SELECT t1.c1,

t2.c2

FROM table1 AS t1

JOIN table1 AS t2

ON t1.c1 = t2.cn;

SQL

Note

We can use any JOIN clause in self-join.

Example

Get student pairs who registered for common course.

1

2

3

4

5

6

7

SELECT sc1.student_id AS student_id1,

sc2.student_id AS student_id2, sc1.course_id

FROM

student_course AS sc1

INNER JOIN student_course sc2 ON sc1.course_id = sc2.course_id

WHERE

sc1.student_id < sc2.student_id;

SQL

Output

student_id1	student_id2	course_id
1	3	11

JOINS Summary

Join Type	Use Case
Natural Join	Joins based on common columns
Inner Join	Joins based on a given condition
Left Join	All rows from left table & matched rows from right table
Right Join	All rows from right table & matched rows from left table
Full Join	All rows from both the tables
Cross Join	All possible combinations