There are times when it is helpful to join two or more tables together.
There’s an option to join a table to itself. It’s called the self-join and it’s used to look at the same set of JOIN is a common term in SQL, and using it is pretty straight forward.
This sentence illustrates how to join a table to itself
SELECT column1, column2, column3, ... FROM table1 A INNER JOIN table1 B ON B.column1 = A.column2;
The join situation of table1 was assigned by statement1 to table2. The alias columns are table1.column1 and table1.column2
You can also use the LEFT JOIN clause for the INNER JOIN clause. Let’s take a few examples of self-joining.
SQL SELF JOIN examples
See the staff table mentioned below
A full-time employee’s manager is indicated by the manager_id column in the employee table. The information of who reports to whom is retrieved from the employees table by the following statement.
SELECT e.first_name || ' ' || e.last_name AS employee, m.first_name || ' ' || m.last_name AS manager FROM employees e INNER JOIN employees m ON m.employee_id = e.manager_id ORDER BY manager;
The president’s row in the table doesn’t have the manager_id column. The president is excluded from the result set returned by the query above due to the clause that factors out the wrapper of the INNER JOIN clause.
To include the president in the result set returned, we use the left JOIN rather than the INNER JOIN clause.
SELECT e.first_name || ' ' || e.last_name AS employee, m.first_name || ' ' || m.last_name AS manager FROM employees e LEFT JOIN employees m ON m.employee_id = e.manager_id ORDER BY manager;