Example of Antijoin, Semijoin, Curtesian Product,Self join

Antijoin Example:
------------------------
If I want to wish to select a list of students who are not in a particular set departments that I can use antijoin as below.

SQL>SELECT * FROM student
WHERE deptid NOT IN
(SELECT deptid FROM dept
WHERE deptid = 3)
ORDER BY NAME;
STDID NAME DEPTID
---------- --------------- ----------
24101 Raju 1

Semijoin Example:
----------------------
Whenever only one row needs to be returned from the departments table, even though many rows in the employees table might match the subquery.
SQL>SELECT * FROM dept
WHERE EXISTS
(SELECT * FROM student
WHERE dept.deptid = student.deptid
)
ORDER BY deptname;
DEPTID DEPTNAME
---------- ----------
3 CSE

Crossjoin Example: Cartesian Product
---------------------------------
SQL> insert into student values(22440,'Adu',2);
1 row created.

SQL> select name,deptname from dept CROSS JOIN student;
NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Adu EEE
Rafi CSE
Raju CSE
Arju CSE
Adu CSE

8 rows selected.

Self Join Example:
---------------------
A self join to return the name of each employee along with the name of the employee's manager.
SELECT e1.last_name||' works for '||e2.last_name
"Employees and Their Managers"
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id

Related Documents:
------------------------
Joins in Oracle
Example of Outer and Equijoin
Difference between inner join and outer join in oracle
Difference between join,inner join, natural join and equijoin in oracle