Menu
I don't understand the need for self-joins. Can someone please explain them to me?
A simple example would be very helpful.
922
A self-join, also known as an inner join, is a structured query language (SQL) statement where a queried table is joined to itself. The self-join statement is necessary when two sets of data, within the same table, are compared.
The SQL SELF JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statement.
A self join allows you to join a table to itself. It helps query hierarchical data or compare rows within the same table. A self join uses the inner join or left join clause.
The self join, as its name implies, joins a table to itself. To use a self join, the table must contain a column (call it X) that acts as the primary key and a different column (call it Y) that stores values that can be matched up with the values in Column X.
You can view self-join as two identical tables. But in normalization, you cannot create two copies of the table so you just simulate having two tables with self-join.
Suppose you have two tables:
emp1 Id Name Boss_id 1 ABC 3 2 DEF 1 3 XYZ 2 emp2 Id Name Boss_id 1 ABC 3 2 DEF 1 3 XYZ 2 Now, if you want to get the name of each employee with his or her boss' names:
select c1.Name , c2.Name As Boss from emp1 c1 inner join emp2 c2 on c1.Boss_id = c2.Id Which will output the following table:
Name Boss ABC XYZ DEF ABC XYZ DEF If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
