Difference between revisions of "PostgreSQL-Database/C2/OUTER-JOINS-and-SELF-JOIN/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
 
Line 88: Line 88:
 
'''Type:'''
 
'''Type:'''
  
−
SELECT * FROM students;
+
'''SELECT * FROM students;'''
  
 
|| We are using the students table.
 
|| We are using the students table.
Line 112: Line 112:
 
|-
 
|-
 
|| Highlight NULL
 
|| 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;'''
 
'''ON students.studentid = results.studentid;'''
  
−
Click on the '''Execute Query''' button.
+
Click on the '''Execute Query''' icon.
  
 
|| Next let us type this query.
 
|| Next let us type this query.
Line 165: Line 165:
 
'''SELF JOIN'''
 
'''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.
 
It is used to compare rows within the same table.
Line 172: Line 172:
 
|| '''Slide 8'''
 
|| '''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'''
 
'''FROM table_name A'''
Line 183: Line 183:
  
 
|| This is the syntax of the '''SELF JOIN'''.
 
|| This is the syntax of the '''SELF JOIN'''.
 +
 
The same table is used twice with aliases A and B.
 
The same table is used twice with aliases A and B.
 +
 
These aliases act as temporary names for each table copy.
 
These aliases act as temporary names for each table copy.
  
Line 202: Line 204:
  
 
|| Now, let us see a simple example of '''SELF JOIN'''.
 
|| Now, let us see a simple example of '''SELF JOIN'''.
 +
 
Let us type this following query.
 
Let us type this following query.
 +
 
This query joins the students table with itself using the city column.
 
This query joins the students table with itself using the city column.
  
Line 217: Line 221:
 
Ram – Ram – Mumbai
 
Ram – Ram – Mumbai
 
|| Each student is matched with others from the same city.
 
|| Each student is matched with others from the same city.
 +
 
Students are also matched with themselves.
 
Students are also matched with themselves.
 +
 
So rows like '''Ram Ram Mumbai''' appear.
 
So rows like '''Ram Ram Mumbai''' appear.
  
Line 227: Line 233:
 
In this tutorial, we have learnt about,
 
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.
 
|| With this, we come to the end of this tutorial.
Line 240: Line 246:
 
As an Assignment,
 
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.
 
|| We encourage you to try this assignment.
Line 254: Line 260:
  
 
|| Thank you.
 
|| Thank you.
−
 
+
|-
 
|}
 
|}

Latest revision as of 12:20, 8 May 2026

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
Slide 1

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

https://EduPyramids.org

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

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

Retrieved from "https://script.spoken-tutorial.org/index.php?title=PostgreSQL-Database/C2/OUTER-JOINS-and-SELF-JOIN/English&oldid=57703"