MySQL Self Join: Examples

Examples
Sample Database

#1

SELECT CONCAT(m.lastname,', ',m.firstname) AS 'Manager',
       CONCAT(e.lastname,', ',e.firstname) AS 'Direct report'	
FROM employees e
INNER JOIN employees m ON m.employeeNumber = e.reportsto
ORDER BY manager

#2

SELECT 
    IFNULL(CONCAT(m.lastname, ', ', m.firstname),
            'Top Manager') AS 'Manager',
    CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
    employees e
        LEFT JOIN
    employees m ON m.employeeNumber = e.reportsto
ORDER BY manager DESC

#3

SELECT 
    c1.city, c1.customerName, c2.customerName
FROM
    customers c1
        INNER JOIN
    customers c2 ON c1.city = c2.city
        AND c1.customername <> c2.customerName
ORDER BY c1.city;

Table List

customers

SELECT * FROM customers;

employees

SELECT * FROM employees;

offices

SELECT * FROM offices;

orderdetails

SELECT * FROM orderdetails;

orders

SELECT * FROM orders;

payments

SELECT * FROM payments;

productlines

SELECT * FROM productlines;

products

SELECT * FROM products;

Result