Oracle SQL Joins

Types of join

Below are the major SQL Joins explained in a simple way.

1. Inner Join
2. Outer Join
2.1 Left Outer Join
2.2 Right Outer Join
3. Self-Join

Left Outer Join

All records from the left table (i.e. emp) even non matching records.

SELECT * FROM emp e,  sample s

WHERE e.empid=s.empid(+)

SELECT * FROM emp e LEFT OUTER JOIN sample s

ON (e.empid=s.empid)

Right Outer Join

SELECT * FROM emp e, sample s

WHERE s.empid=e.empid(+)

SELECT * FROM emp e RIGHT OUTER JOIN sample s

ON (e.empid=s.empid)

Self Join

SELECT e.eid, e.ename employee, m.ename manager

FROM manager e, manager m

WHERE e.mid=m.eid

Inner Join

Joins the 2 tables emp and dept based on the fields deptid

SELECT e.empid, e.empname, d.deptname, e.salary

FROM emp e, dept d

WHERE e.deptid=d.deptid

UNION

Removes the duplicate rows

(SELECT * FROM emp) UNION (SELECT * FROM sample)

UNION ALL

Includes the duplicate rows

(SELECT * FROM emp) UNION ALL (SELECT * FROM sample)

INTERSECT

Returns the common rows from the multiple queries

(SELECT * FROM emp) INTERSECT (SELECT * FROM sample)

MINUS

Return rows present in first query which is not present in second query

(SELECT * FROM emp) MINUS (SELECT * FROM sample)

(SELECT * FROM sample) MINUS (SELECT * FROM emp)


Thanks for reading my Blog ! Enjoy learning :)