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

Featured Image

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:

employee employee_title superior superior_title
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!


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