How to use Self-Join in SQL

Category: SQL :: Published at: 31.12.2021

SQL beginners can have some problems with understanding how Self-Join works. But everybody should understand
how handy this query type is. It helps us to combine two records inside same table into single row.

This thing is widely used on some level of programming and for sure we all sure understand, how to use it.

The first, very optimistic thing is that Self-Join basically have a structure of typical JOIN syntax with some small improvements.

Let's say we have some invoices table

id name company_from company_to proforma_id
1 INVOICE-1 Company A Company D  
2 INVOICE-2 Company B Company C  
3 PROFORMA-1 Company A Company D 12

As you can see, one of invoices has a proforma_id. Proforma is just a preliminary document bill,
when you client will pay money, proforma gets a main invoice.

Let's say we want to get a list of invoices and their proformas.

SELECT invoice.name, proforma.name
FROM invoices AS invoice
JOINS invoices AS proforma ON
invoice.proforma_id = proforma.id 

The code above will treat one table as two seperate tables with different aliases.
We just have created a reference from invoices table to itself.

The result of this query should be:

invoice.name proforma.name
INVOICE-1 PROFORMA-1

And that's it! The syntax here is quite simple, it is important to just understand how it works.


- Click if you liked this article

Views: 94