Self join concept in SQL - 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 do we use table aliases in a self join?

Table aliases give different names to the same table so we 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 employee.manager_id with manager.employee_id using aliases.

intermediate

Can a self join be used with INNER JOIN and LEFT JOIN?

Yes, self joins can use INNER JOIN, LEFT JOIN, or other join types depending on the data needed.

beginner

What is the main difference between a self join and a regular join?

A self join joins a table to itself, while a regular join combines two different tables.

What does a self join do?

A Joins a table to itself

B Joins two different tables

C Joins two databases

D Joins two columns in the same table

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

Why are aliases important in a self join?

A To rename columns

B To speed up the query

C To create new tables

D To refer to the same table twice clearly

Aliases let us give different names to the same table so we can use it twice in the query.

Which join type can be used in a self join?

A Any join type like INNER JOIN or LEFT JOIN

B Only LEFT JOIN

C Only INNER JOIN

D No join type is needed

Self joins can use any join type depending on the data needed.

In a self join, what does the ON clause usually compare?

A Two different tables' columns

B Two unrelated columns

C Two columns from the same table using aliases

D Two databases

The ON clause compares columns from the same table but using different aliases.

What is a common use case for a self join?

A Combining sales data from two tables

B Finding employees with the same manager

C Joining customer and order tables

D Creating a new table

Self joins are often used to find relationships within the same table, like employees sharing a manager.

Explain what a self join is and why we use table aliases in it.

Think about how you can compare rows in the same table.

Describe a real-life example where a self join would be useful.

Consider relationships within one group of people.