Agenda:

JOINS : Joining tables using columns is called joins

2. NON-EQUIJOIN
3. SELF JOIN

NON-EQUIJOIN : Non equijoin is comparing values in 2 or more table, but no = sign is used
All other operators can be used ex. between, not between etc.,

Syntax : SQL> select table1.col, table1.col…
table2.col, table2.col
from table1, table2
where table1.col — no = sign in the where

Sample DATA:

SQL> SELECT * FROM EMP_NEW;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY

---

   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   111 RAM             12000 A          12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

6 rows selected.

SQL> SELECT * FROM SAL_GRADE;

LOW_SAL HIGH_SAL GRADE

---

  1000      10000 a
 10001      20000 b
 20001      30000 c

Example: Compare the sal from emp_new table in sal_grade table and show grade along eno, ename,sal

SQL> SELECT e.eno,
e.ename,
e.sal,
s.grade
from emp_new e,
sal_grade s
where e.sal between s.low_sal and s.high_sal;

   ENO ENAME             SAL GRADE

---

   113 KRISHNA         30000 c
   115 CHANDU          25500 c
   112 RAMESH          25000 c
   105 kumar           12200 b
   111 RAM             12000 b

SELF JOIN: Joining a table to itself is called Self Join

Syntax: SQL> Select t1.col1, t1.col2,
t2.col1, t2.col2…
from table1 t1, table1 t2
where t1.col = t2.col;

Sample Data:

SQL> select * from emp_new;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY               MGR

---

   115 CHANDU          25500 manager    10-MAR-12        222                   114
   105 kumar           12200                             555                   112
   111 RAM             12000 A          12-DEC-14        222                   113
   112 RAMESH          25000 B          29-DEC-14        333                   114
   113 KRISHNA         30000 C          03-JAN-14        555                   114
   114 SHEKAR          56500 manager    01-JAN-14        555                   114

In the above table MGR column has there manager’s eno no
Ex: eno 115 chandu has 114 as manager (114 is eno of Shekar) this means shekar is manager of 115

EXample : Develop a querty to show eno,ename,sal, and their managers name

OUTPUT

   ENO ENAME         SAL   MGR

---

   115 CHANDU          25500  SHEKAR
   105 kumar           12200  RAMESH
   111 RAM             12000  KRISHNA 
   112 RAMESH          25000  SHEKAR
   113 KRISHNA         30000  SHEKAR
   114 SHEKAR          56500  SHEKAR

SQL> SELECT e.eno,
e.ename,
e.sal,
m.ename “manager”
FROM emp_new e,
emp_new m
WHERE e.mgr = m.eno;

Example:

USER_ID USER_NAME CREATION_DATE CREATED_BY

100 OPERATIONS 10-JAN-12 0
101 GATES 12-JAN-12 100
102 JOHN 15-FEB-12 101
103 JOSHEPH 20-MAR-15 100

0 SYS 02-JAN-12 0

OUTPUT

USER_ID USER_NAME CREATION_DATE CREATED_BY

100 OPERATIONS 10-JAN-12 SYS
101 GATES 12-JAN-12 OPERATIONS
102 JOHN 15-FEB-12 GATES
103 JOSHEPH 20-MAR-15 OPERATIONS

0 SYS 02-JAN-12 0

SQL> select a.user_id,
a.user_name,
a.creation_date
b.user_name “Created By”
from user a,
user b
where a.created_by = b.user_id;

How it works

Table : User alias a alias b

USER_ID USER_NAME CREATION_DATE CREATED_BY USER_ID USER_NAME CREATION_DATE CREATED_BY
———————————————————– ——————————————————————–
100 OPERATIONS 10-JAN-12 0 100 OPERATIONS 10-JAN-12 0
101 GATES 12-JAN-12 100 101 GATES 12-JAN-12 100
102 JOHN 15-FEB-12 101 102 JOHN 15-FEB-12 101
103 JOSHEPH 20-MAR-15 100 103 JOSHEPH 20-MAR-15 100
0 SYS 02-JAN-12 0 0 SYS 02-JAN-12 0
———————————————————– ————————————————————————

Share this post: on Twitter on Facebook on Google+