Self join patterns in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a self join in SQL?

A self join is when a table is joined with itself to compare rows within the same table.

beginner

Why use table aliases in a self join?

Aliases give different names to the same table so you can refer to it twice in the query clearly.

intermediate

Example: How to find employees who have the same manager using self join?

Use a self join on the employee table matching manager_id to employee_id with aliases for clarity.

intermediate

What is the difference between INNER JOIN and LEFT JOIN in self join patterns?

INNER JOIN returns only matching rows between the table and itself, LEFT JOIN returns all rows from the left side even if no match.

advanced

How can self joins help in hierarchical data queries?

Self joins let you compare parent and child rows in the same table to explore hierarchy levels.

What keyword is essential to distinguish the same table twice in a self join?

A HAVING

B GROUP BY

C DISTINCT

D Alias

Aliases let you give different names to the same table so you can join it with itself.

In a self join, if you want all rows from the left table even if no match exists on the right, which join do you use?

A LEFT JOIN

B RIGHT JOIN

C INNER JOIN

D FULL JOIN

LEFT JOIN returns all rows from the left table and matches from the right or NULL if no match.

Which of these is a common use case for self joins?

A Joining two different tables

B Comparing rows within the same table

C Filtering rows with WHERE

D Creating indexes

Self joins compare rows within the same table by joining it to itself.

What does the ON clause specify in a self join?

A The grouping of rows

B The order of rows

C How to match rows between the two aliases of the same table

D The columns to select

The ON clause defines the condition to match rows between the two aliases of the same table.

If you want to find employees who share the same manager using a self join, which columns would you join on?

A employee.manager_id = manager.employee_id

B employee.employee_id = manager.employee_id

C employee.name = manager.name

D employee.salary = manager.salary

You join employee.manager_id to manager.employee_id to find employees with the same manager.

Explain what a self join is and why table aliases are important in it.

Think about how you can use the same table twice in one query.

Describe a real-life example where a self join can be used to find relationships within the same data set.

Consider a company employee list with managers.