PostgreSQL-Database/C2/OUTER-JOINS-and-SELF-JOIN/English

Title of the Script: OUTER JOINS and SELF JOIN.

Author: Anjali Satdive EduPyramids.

Keywords: PostgreSQL, pgAdmin4, Join Clause, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN, syntax, EduPyramids, Video Tutorial.

Visual Cue	Narration
Title Slide	Welcome to this Spoken Tutorial on OUTER JOINS and SELF JOIN.
Slide 2 Learning Objectives	In this tutorial, we will learn about: OUTER JOINS and SELF JOIN
Slide 3 System Requirements • Ubuntu 24.04 LTS • PostgreSQL version 18.1, and • PgAdmin4 version 9.11.	To record this tutorial, I am using the following setup.
Slide 4 Pre-requisites	To follow this tutorial, Learners should have PostgreSQL and pgAdmin4 installed in their systems. For the prerequisite PostgreSQL tutorials, please visit this website.
Slide 5 Code Files The following code file is required to practice this tutorial. • lrf-commands.txt This file is provided in the Code Files link of this tutorial page. Please download and extract the file.	The following code file is required to practice this tutorial. This file is provided in the Code Files link of this tutorial page. Please download and extract the file.
Slide 6 OUTER JOIN	Now we will learn about OUTER JOIN. It includes LEFT JOIN, RIGHT JOIN and FULL JOIN. LEFT JOIN returns all rows from the left table with matching right rows. RIGHT JOIN returns all rows from the right table with matching left rows. FULL JOIN returns all rows from both tables and matches them wherever possible.
Show students-table Type: SELECT * FROM students;	We are using the students table. It contains studentid, studentname, city, gender, date of birth, and cgpa.
Type: SELECT students.studentid, studentname, marks FROM students LEFT JOIN results ON students.studentid = results.studentid;	Let us type the following query. This query performs a LEFT JOIN between the students and results tables.
Click on the Execute icon.	Let us execute the query to see the output.
Output displayed	Notice that all records from the students table are displayed.
Highlight NULL	If there is no matching record in the results table, the marks column shows NULL.
Type: SELECT students.studentid, studentname, marks FROM students RIGHT JOIN results ON students.studentid = results.studentid; Click on the Execute Query icon.	Now let us type this following query. This query performs a RIGHT JOIN between the students and results tables. Let us execute the query to see the output.
Highlight output	Notice that all records from the results table are displayed.
Type: SELECT students.studentid, studentname, marks FROM students FULL JOIN results ON students.studentid = results.studentid; Click on the Execute Query button.	Next let us type this query. This will perform a FULL JOIN between the students and results tables. Let us execute the query to see the output. The FULL JOIN shows all the matching records from both tables.
	Now, let us see how a SELF JOIN works.
Slide 7 SELF JOIN	A SELF JOIN joins a table with itself using different aliases. It is used to compare rows within the same table.
Slide 8 Syntax of SELF JOIN SELECT A.column_name, B.column_name FROM table_name A JOIN table_name B ON A.common_column = B.common_column;	This is the syntax of the SELF JOIN. The same table is used twice with aliases A and B. These aliases act as temporary names for each table copy. The ON clause defines how the two copies are linked.
Type: SELECT A.studentname, B.studentname, A.city FROM students A JOIN students B ON A.city = B.city; Click on the Execute Query icon.	Now, let us see a simple example of SELF JOIN. Let us type this following query. This query joins the students table with itself using the city column. Let us execute the query to see the output.
Show output	Notice that students belonging to the same city are displayed together.
Highlight output: Ram – Ram – Mumbai	Each student is matched with others from the same city. Students are also matched with themselves. So rows like Ram Ram Mumbai appear.
Slide 10 Summary In this tutorial, we have learnt about, • OUTER JOINS and SELF JOIN	With this, we come to the end of this tutorial. Let us summarise.
Slide 11 Assignment As an Assignment, • Insert one new student without marks • Execute the OUTER JOIN on it • Display marks by using SELF JOIN	We encourage you to try this assignment.
Slide 12 Thank you This Spoken Tutorial is brought to you by EduPyramids Educational Private Limited SINE IIT Bombay.	Thank you.

Contributors and Content Editors

ANJALISATDIVE, Madhurig