SQL Self Join Example | Self Join in SQL Tutorial
SQL Self Join is the kind of join in which a table is joined with itself means we are joining a table with that same table (which is also called Unary relationships), especially when the table has the FOREIGN KEY which references its PRIMARY KEY. If we want to join the table itself means that each row of the table is combined with itself and with every other row of the table.
The self join allows you to join the table to itself. It is useful for querying the hierarchical data or comparing rows within the same table. Till now, we have seen the Outer Join, Cross Join, Left Join, SQL Joins Overview and Inner Join in this blog.
#SQL Self JOIN Key Points
- The self JOIN occurs when a table takes a ‘selfie.’
- The self JOIN is a regular join, but the table is joined with itself.
- It can be useful when modeling hierarchies.
- They are also useful for comparisons within the table.
- You use the self join when the table references data in itself.
- One most used example is where you wanted to get a list of employees and their immediate managers.
SQL Self Join Example
The self join uses an inner join or left join clause. Because the query that uses the self join references a same table, the table alias is used to assign the different names to a same table within the query. The self-join can be viewed as a join of two copies of the same table.
The table is not copied, but SQL performs a command as though it were. The syntax of a command for joining a table to itself is almost the same as that for the joining two different tables.
If we want to distinguish the column names from one another, aliases for an actual table name are used, since both the tables have the same name. Table name aliases are defined in a FROM clause of the SELECT statement.
#Self JOIN Syntax
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
In the above syntax, T1 and T2 are different table aliases for the same table.
See the following table.
#SQL Self JOIN Example
The following SQL statement matches Products that have different categories.
SELECT A.ProductID, A.ProductName AS ProductNameA, B.ProductName AS ProductNameB, A.Category FROM Products A, Products B WHERE A.`ProductName` <> B.`ProductName` AND A.`Category` = B.`Category` ORDER BY B.`ProductName`;
See the following output.
Finally, SQL Self Join example tutorial is over.