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

Madhurig at 06:50, 8 May 2026

2026-05-08T06:50:26Z

Madhurig at 06:29, 8 May 2026

2026-05-08T06:29:59Z

Madhurig at 06:29, 8 May 2026

2026-05-08T06:29:43Z

ANJALISATDIVE: Created page with "'''Title of the Script:''' OUTER JOINS and SELF JOIN. '''Author:''' Anjali Satdive EduPyramids. '''Keywords:''' PostgreSQL, pgAdmin4, Join Clause, LEFT JOIN, RIGHT JOIN, FU..."

2026-05-08T05:56:44Z

Created page with "'''Title of the Script:''' OUTER JOINS and SELF JOIN. '''Author:''' Anjali Satdive EduPyramids. '''Keywords:''' PostgreSQL, pgAdmin4, Join Clause, LEFT JOIN, RIGHT JOIN, FU..."

New page

'''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.

{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''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.

|}

@@ Line 88: / Line 88: @@
 '''Type:'''
-SELECT * FROM students;
+'''SELECT * FROM students;'''
 || We are using the students table.
@@ Line 112: / Line 112: @@
 |-
 || Highlight NULL
-|| If there is no matching record in the results table, the marks column shows NULL.
+|| If there is no matching record in the results table, the marks column shows '''NULL'''.
 |-
@@ Line 148: / Line 148: @@
 '''ON students.studentid = results.studentid;'''
-Click on the '''Execute Query''' button.
+Click on the '''Execute Query''' icon.
 || Next let us type this query.
@@ Line 165: / Line 165: @@
 '''SELF JOIN'''
-|| A '''SELF JOIN''' joins a table with itself using different aliases.
+|| A '''SELF JOIN''' joins a table with itself using different '''aliases'''.
 It is used to compare rows within the same table.
@@ Line 172: / Line 172: @@
 || '''Slide 8'''
-'''Syntax of SELF JOIN
+'''Syntax of SELF JOIN'''
-SELECT A.column_name, B.column_name'''
+'''SELECT A.column_name, B.column_name'''
 '''FROM table_name A'''
@@ Line 183: / Line 183: @@
 || 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.
@@ Line 202: / Line 204: @@
 || 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.
@@ Line 217: / Line 221: @@
 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.
@@ Line 227: / Line 233: @@
 In this tutorial, we have learnt about,
-• '''OUTER JOINS''' and '''SELF JOIN'''
+* '''OUTER JOINS''' and '''SELF JOIN'''
 || With this, we come to the end of this tutorial.
@@ Line 240: / Line 246: @@
 As an Assignment,
-• Insert one new student without marks
+* Insert one new student without marks
-• Execute the '''OUTER JOIN''' on it
+* Execute the '''OUTER JOIN''' on it
-• Display marks by using '''SELF JOIN'''
+* Display marks by using '''SELF JOIN'''
 || We encourage you to try this assignment.
@@ Line 254: / Line 260: @@
 || Thank you.
+|-
 |}

@@ Line 11: / Line 11: @@
 ! width="65%" | '''Narration'''
 |-
-||''' Slide 1'''
+|| '''Slide 1'''
 '''Title Slide'''
@@ Line 31: / Line 31: @@
 '''System Requirements'''
-• '''Ubuntu 24.04''' LTS
+* '''Ubuntu 24.04''' LTS
-• '''PostgreSQL version 18.1''', and
+* '''PostgreSQL version 18.1''', and
-• '''PgAdmin4 version 9.11'''.
+* '''PgAdmin4 version 9.11'''.
 || To record this tutorial, I am using the following setup.
@@ Line 41: / Line 41: @@
 '''Pre-requisites'''
+[https://edupyramids.org/ https://EduPyramids.org] '''
 || To follow this tutorial,
@@ Line 57: / Line 57: @@
 The following '''code file''' is required to practice this tutorial.
-• '''lrf-commands.txt'''
+* '''lrf-commands.txt'''
 This file is provided in the '''Code Files''' link of this tutorial page.
@@ Line 70: / Line 70: @@
 |-
-|| '''Slide 6
+|| '''Slide 6'''
-OUTER JOIN'''
+'''OUTER JOIN'''
 || Now we will learn about '''OUTER JOIN.'''

@@ Line 11: / Line 11: @@
 ! width="65%" | '''Narration'''
 |-
-|| '''Title Slide'''
+||''' Slide 1'''
 || Welcome to this Spoken Tutorial on '''OUTER JOINS''' and '''SELF JOIN'''.
 |-
 || '''Slide 2'''
 '''Learning Objectives'''
 || In this tutorial, we will learn about:
@@ Line 37: / Line 40: @@
 '''Pre-requisites'''
 || To follow this tutorial,
@@ Line 55: / Line 60: @@
 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.