SQL SELF JOIN Example

SELF-JOIN is similar to any other join with the exception of that in self join we join a table to itself. In other words we treat one table as two different tables and then join them to get desired output.

Example :-

Category table is part of most of the websites, which is used to categorize products or articles in a web site. Some database persons are always confused in creating category, subcategory tables, this articles is for them also.

A category table should look something like following

Now lets create a query to extract category_id, category_name, parent _category_id, parent_category_name

select a.category_id,a.category_name,b.category_id as parent_id,b.category_name as parent_name
from category a join category b
on a.category_parent=b.category_id

Result