MYSQL SELF JOIN
MYSQL
A SELF JOIN in MySQL is a technique that joins a table to itself to compare rows within the same table.
MySQL self join
➔ SELF JOIN is mainly used to work with hierarchical data (such as category sub-categories) within a single table.
➔ Table aliases are mandatory for SELF JOINs because they allow MySQL to distinguish between two instances of the same table that are referenced in a single query.
➔ The technique involves here in self join to reference the same table name twice, each with a unique alias, and specify the join conditions using those aliases.
Problem definition:
Problem: Find the manager name of each emplyee from table emp with columns employeeId, emplyeeName, managerId.
Create table:
Example
/* Create table */
CREATE TABLE emp (
employeeId INT PRIMARY KEY,
employeeName VARCHAR(255) NOT NULL,
managerId INT
);
Copy the code and try it out practically in your learning environment.
Insert value:
Example
/* Insert values */
INSERT INTO emp (employeeId, employeeName, managerId)
VALUES (1, "John Smith", null);
INSERT INTO emp (employeeId, employeeName, managerId)
VALUES (2, "Mary Johnson", 1);
INSERT INTO emp (employeeId, employeeName, managerId)
VALUES (3, "Sam Brown", 1);
INSERT INTO emp (employeeId, employeeName, managerId)
VALUES (4, "Alice White", 2);
Copy the code and try it out in your learning environment.
Describe table structure:
Example
/* Check table structure */
DESCRIBE emp;
Copy the code and try it out practically in your learning environment.
Table structure:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| employeeId | int(11) | NO | PRI | ||
| employeeName | varchar(255) | NO | |||
| managerId | int | YES |
Query value:
Example
/* Query data */
SELECT * FROM emp;
Copy the code and try it out practically in your learning environment.
Table Data:
| employeeId | employeeName | managerId |
|---|---|---|
| 1 | John Smith | null |
| 2 | Mary Johnson | 1 |
| 3 | Sam Brown | 1 |
| 4 | Alice White | 2 |
SELF JOIN:
Solve: Find the manager name of each emplyee from table emp with columns employeeId, emplyeeName, managerId.
Example
/* Create Self join using LEFT JOIN */
SELECT e.employeeName AS Employee,
m.employeeName AS Manager
FROM emp AS e
LEFT JOIN emp AS m ON e.managerId = m.employeeId;
Copy the code and try it out practically in your learning environment.
Self Join output:
| Employee | Manager |
|---|---|
| John Smith | null |
| Mary Johnson | John Smith |
| Sam Brown | John Smith |
| Alice White | Mary Johnson |
SELF JOIN:
Example
/* Create Self join using WHERE */
SELECT e.employeeName AS Employee, m.employeeName AS Manager
FROM emp e, emp m
WHERE e.managerId = m.employeeId;
Copy the code and try it out practically in your learning environment.
SELF JOIN:
Example
/* Create Self join using INNER JOIN */
SELECT e.employeeName AS Employee, m.employeeName AS Manager
FROM emp e INNER JOIN emp m
ON e.managerId = m.employeeId;
Copy the code and try it out practically in your learning environment.