A self join is where a table joins to itself. Both are expensive. Both have uses and are sometimes the right choice, depending on your situation.

Is join Operation expensive?

Joins involving properly selected keys with correctly set up indexes are cheap, not expensive, because they allow significant pruning of the result before the rows are materialised. Materialising the result involves bulk disk reads which are the most expensive aspect of the exercise by an order of magnitude.

Is self join efficient?

While self-joins do indeed have their place, and can be very powerful and useful, often times there is a much easier and more efficient way to get the results you need when querying a single table. This can be extended to find any number of matching values, and we can even put them in a table and JOIN to it.