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
Joins in Oracle.
In this section I have given definition of several join terms. In another section inshallah I will demonstrate example of joins in oracle.
What is Join?
---------------------
•In a straightforward a join is a query that combines rows from two or more tables, views, or materialized views.
•Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query.
•In join if any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
Join Conditions
-------------------------
•Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause.
•The join condition compares two columns, each from a different table.
•To execute a join, Oracle Database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE.
•To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result.
•The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.
•WHERE clause that contains a join condition can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.
Types of Joins in Oracle:
-----------------------------------
A)Equijoins
------------------------
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.
B)Self Joins
-----------------
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.
C)Cartesian Products
--------------------------
If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful.
D)Inner Joins
-------------------
An inner join is a join of two or more tables that returns only those rows that satisfy the join condition. It is also called simple join.
E)Outer Joins
------------------------
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
Outer join can be categorizes into three.
a)Left Outer Join: To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.
b)Right Outer Join:To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
c)Full Outer Join:To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.
E)Antijoins
---------------
An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. That is, it returns rows that fail to match (NOT IN) the subquery on the right side.
F)Semijoins
---------------------
A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.
Semijoin and antijoin transformation cannot be done if the subquery is on an OR branch of the WHERE clause.
Examples of All outer joins and Equijoins in oracle
In previous post I have define all types of joins in oracle. In this post I will show example of all outer joins one by one. Also I have shown an example of equijoins. Hopefully I next post I will give example of semijoin, antijoin, selfjoin and cartesian product.
To demonstrate example I have used two table customers and orders and insert some data into it.
/* create the customer table */
Create Table Customers (
CustNo Integer Not Null Primary Key,
CustName Char (20),
Address Char (40)
);
/* create the orders table */
Create Table Orders (
OrderNo Integer Not Null Primary Key,
CustNo Integer,
OrderDate Date
);
/* put some data into the customer table */
Insert into Customers Values (1,'P. Jones','Leeds');
Insert into Customers Values (2,'A. Chan','Hong Kong');
Insert into Customers Values (3,'K. Green','Columbia');
Insert into Customers Values (4,'B. Smith','Leeds');
Insert into Customers Values (5,'A. Khan','');
/* put some data into the orders table */
Insert into Orders Values (1,1,'24-JAN-96');
Insert into Orders Values (2,1,'31-JAN-96');
Insert into Orders Values (3,2,'04-FEB-96');
Insert into Orders Values (4,4,'12-FEB-96');
Insert into Orders Values (5,6,'12-FEB-96');
Now have a look at both tables' data.
SQL> select * from customers;
CUSTNO CUSTNAME ADDRESS
---------- -------------------- ----------------------------------------
1 P. Jones Leeds
2 A. Chan Hong Kong
3 K. Green Columbia
4 B. Smith Leeds
SQL> select * from orders;
ORDERNO CUSTNO ORDERDATE
---------- ---------- ---------
1 1 24-JAN-96
2 1 31-JAN-96
3 2 04-FEB-96
4 4 12-FEB-96
5 6 12-FEB-96
Example of Left Outer Join:
-------------------------------
SQL> select CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo = o.CustNo(+) and c.address='Leeds';
or recommended later 9i syntax,
SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo
where c.Address = 'Leeds'; 2 3 4
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
B. Smith 12-FEB-96 Leeds
Right Outer Join Example:
----------------------------
SQL> select CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo(+) = o.CustNo and o.OrderDate > '31-JAN-96';
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96
or recommended later 9i syntax
SQL> select CustName, OrderDate, Address
from Customers c Right outer join Orders o
on c.CustNo = o.CustNo
where o.OrderDate > '31-JAN-96';
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96
Full outer join Example:
----------------------------
SQL> select CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo
where o.OrderDate > '31-JAN-96';
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96
Without condition here is the example of full, left and right outer join.
SQL> select CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
K. Green Columbia
12-FEB-96
6 rows selected.
SQL> select CustName, OrderDate, Address
from Customers c right outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 31-JAN-96 Leeds
P. Jones 24-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96
SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
K. Green Columbia
Example of Equijoins
--------------------------
SQL> select address, orderdate from customers c, orders o where c.custno=o.custno order by orderdate;
ADDRESS ORDERDATE
---------------------------------------- ---------
Leeds 24-JAN-96
Leeds 31-JAN-96
Hong Kong 04-FEB-96
Leeds 12-FEB-96
Difference between inner join and outer join
An inner join will return a row only if there is a joined row with data in both tables- being joined. An outer join will return a row even if the other table doesn't have a corresponding row.
With an example I will make the scenarios clear. I will create two tables named student and dept. In the student table there will be one department but the corresponding department is not exist in dept table. Now I perform both inner join and outer join on deptid column.
SQL> create table student(stdid number, name varchar2(15),deptid number);
Table created.
SQL> insert into student values (024413,'Rafi',3);
SQL> insert into student values (024101,'Raju',1);
SQL> insert into student values (024434,'Arju',3);
SQL> create table dept(deptid number,deptname varchar2(10));
Table created.
SQL> insert into dept values(2,'EEE');
SQL> insert into dept values(3,'CSE');
SQL> select s.name,d.deptname from dept d INNER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE
See in case of INNER join rows return only if joined row exist with data in both tables.
SQL> select s.name,d.deptname from dept d RIGHT OUTER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Arju CSE
Rafi CSE
Raju
SQL> select s.name,d.deptname from dept d LEFT OUTER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE
EEE
SQL> select s.name,d.deptname from dept d FULL OUTER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE
EEE
Raju
As we see in case of OUTER join it return a row even if the other table doesn't have a corresponding row.
Difference between join, Inner join,Equijoin and Natural Join
An inner join is a join with a join condition that may contain both equality or non-equality sign whereas an equijoin is a join with a join condition that only contain only equality sign.
So we can say an equijoin is a type of inner join containing (Equal)= operator in the join condition.
It is good to know the difference between join and INNER JOIN keywoed. Actually there is no difference. If we write JOIN then by default INNER JOIN is performed. In the example it is also shown.
The following example will make you more clear.
In this example I used data as in example of Difference between Inner join and Outer join
SQL> select s.name,d.deptname from dept d, student s where d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE
This example represents both INNER join and equijoin.
SQL> select s.name,d.deptname from dept d INNER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE
Above example also represents both INNER join and equijoin.
SQL> select s.name,d.deptname from dept d INNER JOIN student s on d.deptid<>s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Raju CSE
Above example represents an inner join but not a equijoin.
SQL> select s.name,d.deptname from dept d JOIN student s on d.deptid<>s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Raju CSE
Above example show JOIN and INNER join keyword is same. If we don't specify INNER then by default inner join is performed.
Now let's have a look at natural join.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTID NUMBER
DEPTNAME VARCHAR2(10)
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
STDID NUMBER
NAME VARCHAR2(15)
DEPTID NUMBER
After describing both table we see both table have a same column name deptid. Now if I perform natural join then tables with same column name is joined.
SQL> select s.name,d.deptname from dept d NATURAL JOIN student s ;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE
As both table have same column deptid so deptid is joined.
Now I rename deptid column and see the result. We will notice in that case inner join will be performed.
SQL> alter table student rename column deptid to deptid1;
Table altered.
SQL> select s.name,d.deptname from dept d NATURAL JOIN student s ;
NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE
6 rows selected.
As both table don't have same column name so normal join/inner join is performed.
SQL> select s.name,d.deptname from dept d, student s ;
NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE
6 rows selected.
SQL> select s.name,d.deptname from dept d CROSS JOIN student s ;
NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE
6 rows selected.
No comments:
Post a Comment