Self-join query experiment of oracle rookie learning

Creation of Experimental Table of self-join query for oracle Rookie Learning

Table field description:

Id: employee number

Name: employee name

Ano: manager number

Create table admin (id varchar2 (4), name varchar2 (10), ano varchar2 (4)); insert into admin values ('004'); insert into admin values (' 002'); insert into admin values ('003'); insert into admin values (' 004')

View tabl

SQL > select * from admin;ID NAME ANO--001 XiongDa 004002 XiongEr 004003 ZhangSan 003004 ZhaoSi 004SQL > problem

Display the number, name and manager name information by querying the admin table

Experimental procedure

Main idea: how to find the corresponding name of ano

The corresponding relationship between id and ano

When we query two tables, virtually all rows of the two tables are cross-linked

SQL > select * from admin a, admin b ID NAME ANO ID NAME ANO 001 XiongDa 004001 XiongDa 004001 XiongDa 004002 XiongEr 004001 XiongDa 004003 ZhangSan 003001 XiongDa 004004 ZhaoSi 004002 XiongEr 004 001 XiongDa 004002 XiongEr 004002 XiongEr 004002 XiongEr 004003 ZhangSan 003002 XiongEr 004 004 ZhaoSi 004003 ZhangSan 003 001 XiongDa 004003 ZhangSan 003 002 XiongEr 004003 ZhangSan 003003 ZhangSan 003003 ZhangSan 003 004 ZhaoSi 004004 ZhaoSi 004 001 XiongDa 004004 ZhaoSi 004 002 XiongEr 004004 ZhaoSi 004 003 ZhangSan 003004 ZhaoSi 004004 ZhaoSi 00416 rows selected.

The data we need can be seen through the human eye, and the information we want can be obtained by writing the name of the second table in the ano of the first table.

001 XiongDa 004 004 ZhaoSi 004002 XiongEr 004 004 ZhaoSi 004003 ZhangSan 003 003 ZhangSan 003004 ZhaoSi 004 004 ZhaoSi 004

Through the above results to find the corresponding relationship, it is found that as long as ano=id, then the result can be obtained.

SQL > select a. ID. A. Name as aname from admin a, admin b where a.ano=b.id ID NAME ANAME--003 ZhangSan ZhangSan004 ZhaoSi ZhaoSi002 XiongEr ZhaoSi001 XiongDa ZhaoSiSQL >