Self JOIN in SQL

SQL SELF JOIN works the same way as the JOIN keyword itself. The only difference is that SELF JOIN joins the columns form a table itself.

Take a look at the syntax:

SELECT columns
FROM table Aliases_1, table Aliases_2 WHERE condition;

 So as we can in the syntax above, the columns are from the same tables and we just join it to get desired results. We can use Aliases to give the columns different names in the result set.

Let’s take a look at the example.

SELECT b.CustomerName AS [Customer Name], b.ContactName AS [Contact Name]
FROM customers a, customers b 
WHERE a.ContactName=b.ContactName 
  and a.CustomerName='Janis D. Triplett';

That is an example for SQL.

NOTE: Square brackets are used in the aliases’ names as they contain spaces.

Now take a look at the example for MySQL. There is just slight difference. If you’re using phpmyadmin then you need to use the following example.

SELECT b.CustomerName, b.ContactName 
FROM customers AS a, 
      customers AS b 
WHERE a.ContactName=b.ContactName 
  and a.CustomerName='Janis D. Triplett';

We used aliases in the column names in SQL and in MySQL, we are using it for tables names. 

Both of the above example will give same results and they will find customer’s name and related contact person for that customer. 

Share this post