The MySQL self join, along with the different types of joins that are offered by SQL are important to be learned by anyone who wants to use DBMS or Database Management Systems to do their work in databases. The SQL language offers multiple types of joins which one can use to join different tables together to collectively extract and load information. In this article, we’ll go over understanding the Self Join in an easy way and with multiple examples so you can get it going in your database work accurately.
Also, read-> Download MySQL Cheatsheet PDF
What are SQL Joins?
In a database management system like SQL, if you have multiple tables (2 or more) and you want to extract data out of them collectively where the data is connected using a primary or secondary key, the data can be joined together using SQL joins which allow users to interact with the data in multiple data tables together and get information as required.
You may use SQL joins especially in a schema format in your company or projects where the data is connected effectively together from different departments using tables and a unique identifier for each table.
The different types of joins in SQL are;
- SQL INNER JOIN
- SQL LEFT OUTER JOIN
- SQL RIGHT OUTER JOIN
- SQL FULL OUTER JOIN
- SQL SELF JOIN
Check out this quick tutorial if you are new to SQL -> Free SQL Tutorials
What is MySQL?
MySQL is a renowned, free, open-source relational database management system or RDBMS, that uses Structured Query Language (SQL), one of the or probably the most popular computer languages for adding, accessing, and processing data in a database. MySQL is noted for its speed, reliability, and flexibility, and has been widely used for website applications since the mid-1990s after which many more alternatives like PostgreSQL and SQLite have come into the picture but nevertheless, MySQL still is cited as one of the best RDBMS. Let’s get to what the MySQL self join is.
What is MySQL Self Join?
The terms of the join are pretty much self-explanatory. The join works on joining the tables together, the only difference is that in the MySQL self join, you are joining a table, essentially with itself. This can be due to a lot of reasons that you need to do it and though, not as famous as the inner or outer join, it is important to know how to execute the MySQL self join when you have to in your work.
The best way to do a Self Join is by using table aliases. They help in avoiding the same table name twice within a single statement, this is to avoid an error that occurs on using the multiple instances of the same table name within a query. Therefore, a table alias is more like a temporary name of the table in the MySQL self join which is essential for the RDBMS to execute the command without any error.
Examples for MySQL Self Join
Let us understand the MySQL self join in a better way using some examples.
A self join is a regular join, but the table is joined with itself
Note: A and B are different table aliases for the same table.
SELECT column_name(s) FROM table1 A, table1 B WHERE some_condition;
You can also write the syntax as follows, where the syntax of self-join is similar to the syntax used when joining two different tables. Only here the names for the tables are aliases because both the table names are the same.
SELECT s1.col_name, s2.col_name... FROM table1 s1, table1 s2 WHERE s1.common_col_name = s2.common_col_name;
Let us look at some examples of MySQL self joins now:
Consider a situation where you want to know if by error, in a table relating to labourers, you have the ID numbers corresponding and duplicated in the task number columns as well. Knowing you have only 5 labourers i.e. A, B, C, D, E to look into, the data is provided to you, and with a simple self join in MySQL, you can identify whether, for some laborers, the ID number has, in fact, been replicated and used as the task number which should not be the case as per the problem statement.
Consider the following table:
| ID number | Labourer | Task Number | Hours |
| 1 | A | 1 | 4 |
| 2 | B | 2 | 5 |
| 3 | C | 2 | 7 |
| 4 | D | 3 | 4 |
| 5 | E | 3 | 5 |
If you wanted to Self join this table and find out the laborers whose ID number and Task number are not equal, then the code would look as follows;
SELECT L1.ID_number, L1.labourer FROM Labourers AS L1, Labourers AS L2 WHERE L1.ID_number = L2.ID_number AND L1.Task_Number <> L2.Task_Number;
The output for the self-join would look like this (note the repetitions):
| ID_number | Labourer |
| 1 | A |
| 2 | B |
| 1 | A |
| 2 | B |
Similarly, if you have a table with customers who need to be joined with themselves based on them being from the same city, it can be done as follows;
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City;
Try out this self join here -> Self join – trial
Conclusion
Understanding how joins work in DBMS like SQL and using it in MySQL is one of the most important things to learn in the SQL Language after CRUD commands. Harnessing the power of joins will enable a data scientist or researcher to identify trends in data by connecting it to different sources which is important as all the data you will need in your workings will never come from just one dataset.
However rare it may be to use a MySQL self join, it’s important to know it. A simple concept like this can stall your entire analysis if you don’t know how to do it.
Try doing a MySQL self join using table aliases today and let us know what you think about it in the comments below.
For more such content, check out our website -> Buggy Programmer
An eternal learner, I believe Data is the panacea to the world's problems. I enjoy Data Science and all things related to data. Let's unravel this mystery about what Data Science really is, together. With over 33 certifications, I enjoy writing about Data Science to make it simpler for everyone to understand. Happy reading and do connect with me on my LinkedIn to know more!
