MySQL – Left, Right, Outer or Inner Join? No – the SELF JOIN

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:

employeeemployee_titlesuperiorsuperior_title
Name 1Junior developer 1Name 3Senior developer 3
Name 2Junior developer 2Name 3Senior developer 3
Name 3Senior developer 3Name 4Project 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!

You made it all the way down here so you must have enjoyed this post! You may also like:

Create Windows virtual hosts for your Magento projects Nikola Stojiljkovic
Nikola Stojiljkovic, | 8

Create Windows virtual hosts for your Magento projects

Solving problems with Category tree database information Nikola Stojiljkovic
Nikola Stojiljkovic, | 19

Solving problems with Category tree database information

How to add new custom category attribute in Magento Vedran Subotic
Vedran Subotic, | 83

How to add new custom category attribute in Magento

4 comments

  1. Hi,

    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!

  2. 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.

  3. 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.

  4. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.