Yogesh Chauhan's Blog

Learn how to use Self JOIN in SQL and MySQL

in SQL/MySQL on 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. 

Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
Object destructuring in JavaScript: Unpacking fields from objects passed as function parameterJavaScriptHow to Sort (Shuffle) an Array in Random Order in JavaScript?JavaScriptFilling a button background from left to right using CSSCSSCSS Overflow Property with ExamplesCSSAdd animated hamburgers menu using Hamburgers collection on GitHubCSSHow to convert an object from API to JSON array in Angular 9?Angular