Learn how to use Self JOIN in SQL and MySQL
September 24, 2019

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. 


Leave a Reply

Most Read

#1 How to set opacity or transparency using CSS? #2 Pagination in CSS with multiple examples #3 How to check if radio button is checked or not using JavaScript? #4 How to make HTML form interactive and using CSS? #5 How to uninstall Cocoapods from the Mac OS? #6 How to add Read More Read Less Button using JavaScript?

Recently Posted

Feb 24 How to modify the latest post array using get_posts() in WordPress? Feb 22 WordPress: How to get ACF field values from another post? Feb 22 WordPress: How to print ACF repeater field values? Feb 22 WordPress: How to print ACF array field values? Feb 21 WordPress: How to get field values in Advanced Custom Fields? Feb 21 WordPress: How to add a Search Icon in Menus with toggle effect using jQuery?

You might also like these

Containers 101: What are containers?MiscWhy does MOV matter in IT or any kind of projects?Misc5 Ways to Loop Through JavaScript ArraysJavaScriptCasting in PostgreSQLPostgresMySQL queries to get data rows from previous month as well as from last 30 daysSQL/MySQLHow to create ‘share on LinkedIn’ link using just HTML?HTML