MySQL – Left, Right, Outer or Inner Join? No – the SELF JOIN
Hi to all of you! Today, I’m going to explain SELF JOIN in MySQL – I’ll answer why and then how should you use it.
But first, a short explanation. I’m going to explain a non often used SQL that came in hand recently. If you are still learning MySQL, please refer to some basics first – a good start. And now, to the fun part. 😀
Why do anyone need SELF JOIN?
Well, SQL self-join is very useful when you want to retrieve related data stored in one table such as organizational structure. Its basically an inner join with reference on the table itself.
And a practical example of usage:
In our DB sample, we have employees table which stores not only employee data but also organizational structure.
SQL for sample table:
CREATE TABLE `test`.`employees` ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 50 ) NOT NULL , `title` VARCHAR( 50 ) NOT NULL , `superior` INT( 11 ) NOT NULL ) ENGINE = MYISAM ;
And SQL for sample values:
INSERT INTO `employees` (id,name,title,superior) VALUES (NULL,"Name 1","Junior developer 1",3), (NULL,"Name 2","Junior developer 2",3), (NULL,"Name 3","Senior developer 3",4), (NULL,"Name 4","Project manager",0);
And finally, self join would look like:
SELECT B.name employee, B.title employee_title, A.name superior, A.title superior_title FROM employees AS A INNER JOIN employees AS B ON A.id = B.superior
The result of this query with this sample database table, and this dataset (that we inserted) would produce a result like this:
|Name 1||Junior developer 1||Name 3||Senior developer 3|
|Name 2||Junior developer 2||Name 3||Senior developer 3|
|Name 3||Senior developer 3||Name 4||Project manager|
This table should be self-explanatory but if you aren’t sure what just happened, please do experiment with your sample database table.
Note: You can see that there’s no “SELF JOIN” syntax but rather inner join on same table.
I hope this article helped you!
I agree this is not something fancy, but, I’ll also quote myself “I’m going to explain a non often used SQL that came in hand recently”. 😀
The thing is, that this can help you with let’s say complex menu table (that for example contains n-level menu). Then you avoid the need to pull the menu with more than 1 query.
And one more thing… This article was intended for beginner to intermediate level of knowledge – a simple example… 😀
And thanks for opinions!
The self join, what happens to you when you let all your columns become rows in your database. What’s needed is to port this junk over to MSSql7 and allow for PIVOT which would simplify a lot of the hoop jumping.
This is just a nested SQL select query, double run on the same table. Nothing fancy 🙂 What about making up a design pattern for every combinatorial subset of all possible data relations, i.e. powerset of S^2, and calling it “design patterns”? Exactly. Completely Useless.. Very IT.
But thanks a lot for doing a great job in explaining a fancy name.
and the “nested set” would be a logical (faster) way ahead from parent->slave structure if you have large datasets that need more read operations than writes