What is the query to select empname and his managername from an emp table?
This sounds like another homework question. However, it is also probably the most common example of a self-join ever devised. Everybody uses this example, and it's real easy to find on the Web, so there's no reason why TechTarget shouldn't also publish it.
select e.empname , m.empname as managername from emp as e left outer join emp as m on m.empid = e.managerid
Whenever you join a table to itself, use table aliases to distinguish the "copies" of the table. (The optimizer might not actually use two "copies" of the table, but it is convenient to think of it as working that way.)
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading