Home > Technical > Joins In Oracle SQL

Joins In Oracle SQL

August 2, 2012

Joins in SQL is used to combine two or more tables and retrieve the data to result a set of ouput.

Types of Joins in SQL:

=================

1.Equijoins

2.Non-Equijoins

3.Inner joins

4.Outer joins

5.Natural joins

6.Cross joins

7.Self joins

I am using the below tables to explain the joins

1.CREATE TABLE

ORDERS_KARU

(ORDER_ID NUMBER PRIMARY KEY,
ORDER_NAME VARCHAR2(20),
ORDER_DATE DATE,
QUANTITY NUMBER);

2.CREATE TABLE

CUSTOMERS_KARU

(CUSTOMER_ID NUMBER PRIMARY KEY,
CUSTOMER_NAME VARCHAR2(20),
CUSTOMER_ADDRESS VARCHAR2(50),
ORDER_ID NUMBER CONSTRAINT CUS_FR_KEY REFERENCES ORDERS_KARU(ORDER_ID));

3.INSERT INTO ORDERS_KARU VALUES(1,’KEYBOARDS’,SYSDATE,10);

INSERT INTO ORDERS_KARU VALUES(2,’MOUSE’,SYSDATE,20);

INSERT INTO ORDERS_KARU VALUES(3,’CELL PHONE’,SYSDATE,30);

INSERT INTO ORDERS_KARU VALUES(4,’BOOKS’,SYSDATE,40);

INSERT INTO ORDERS_KARU VALUES(5,’MONITOS’,SYSDATE,50);

ORDER_ID ORDER_NAME ORDER_DATE QUANTITY
1 KEYBOARDS 2012-08-02 04:51:04 10
2 MOUSE 2012-08-02 04:52:39 20
3 CELL PHONE 2012-08-02 04:52:39 30
4 BOOKS 2012-08-02 04:52:39 40
5 MONITOS 2012-08-02 04:52:39 50

4.INSERT INTO CUSTOMERS_KARU VALUES(1,’HP’,’UK’,1);

INSERT INTO CUSTOMERS_KARU VALUES(2,’DELL’,’USA’,1);

INSERT INTO CUSTOMERS_KARU VALUES(3,’ALLEGRO’,’AUS’,3);

INSERT INTO CUSTOMERS_KARU VALUES(4,’IPHONE’,’CHN’,5);

INSERT INTO CUSTOMERS_KARU(CUSTOMER_ID,CUSTOMER_NAME,

CUSTOMER_ADDRESS) VALUES (5,’NOKIA’,’IND’);

CUSTOMER_ID CUSTOMER_NAME CUSTOMER_ADDRESS ORDER_ID
1 HP UK 1
2 DELL USA 1
3 ALLEGRO AUS 3
4 IPHONE CHN 5
5 NOKIA IND

Inner Joins

==========

Inner joins will give the results if and only if the conditions are matched between the joined tables.

1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK INNER JOIN CUSTOMERS_KARU CK
ON

OK.ORDER_ID = CK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

2.SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK INNER JOIN CUSTOMERS_KARU CK
ON

OK.ORDER_ID = CK.ORDER_ID
WHERE

CUSTOMER_NAME LIKE ‘%E%’;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

Older Inner Join Syntax:

====================

1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK,CUSTOMERS_KARU CK
WHERE

OK.ORDER_ID = CK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

2.Outer Joins :

===========

Outer join is same as inner joins but also adds data from rows that dont necessarily matches in joined other tables.

Types of outer joins : Left,Right,Full

2.1.Left Outer Join

2.1.1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK LEFT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
4
2

2.1.2 SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK LEFT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

2.1.3.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK left outer join CUSTOMERS_KARU CK
on

CK.ORDER_ID = OK.ORDER_ID
and

                         CK.customer_name like ‘%E%’;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
4
2

2.1.4.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK LEFT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID
WHERE

                          CK.customer_name like ‘%E%’;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

2.2.Right outer join:

2.2.1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK RIGHT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
5 NOKIA

2.2.2.SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK RIGHT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
5 NOKIA

2.3.Full outer join:

2.3.1SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK FULL OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
4
2
5 NOKIA

Older outer join syntax:

2.4.Older Right outer join

SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK, CUSTOMERS_KARU CK
WHERE

CK.ORDER_ID = OK.ORDER_ID(+);

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
5 NOKIA

2.5. Older Left outer join

2.5.1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK, CUSTOMERS_KARU CK
WHERE

CK.ORDER_ID(+) = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
4
2

2.5.2.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK, CUSTOMERS_KARU CK
WHERE

CK.ORDER_ID(+) = OK.ORDER_ID
and CK.customer_name like ‘%E%’;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

3.Natural Join :
============

If the columns in two tables have identical names, we can do the natural join.

3.1. SELECT * FROM ORDERS_KARU NATURAL JOIN CUSTOMERS_KARU;

O_ID O_NAME O_DATE QUANTITY C_ID C_NAME C_ADDRESS
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN

USING –this kiyword is similar to natural join.

3.2SELECT *

FROM

ORDERS_KARU

JOIN

CUSTOMERS_KARU

USING(ORDER_ID);

O_ID O_NAME O_DATE QTY C_ID C_NAME C_ADDRESS
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN

3.3 SELECT *

FROM

ORDERS_KARU

LEFT OUTER JOIN

CUSTOMERS_KARU

USING(ORDER_ID);

O_ID O_NAME O_DATE QUANTITY C_ID C_NAME C_ADDRESS
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN
4 BOOKS 2012-08-02 04:52:39 40
2 MOUSE 2012-08-02 04:52:39 20

3.4. SELECT *

FROM

ORDERS_KARU

RIGHT OUTER JOIN

CUSTOMERS_KARU

USING(ORDER_ID);

O_ID O_NAME O_DATE QTY C_ID C_NAME C_ADDRESS
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN
5 NOKIA IND

4.Non-EquiJoin

=============

Other than equal s criteria, like >=,<=,!=, like, between….what ever used to join the tables….these joins are called non-equijoin.

4.1 SELECT *

FROM

ORDERS_KARU OK

JOIN

CUSTOMERS_KARU CK

ON OK.ORDER_ID != CK.ORDER_ID;

O_ID O_NAME O_DATE QTY C_ID C_N C_ADR O_ID
1 KEYBOARDS 2012-08-02 04:51:04 10 3 ALLEGRO AUS 3
1 KEYBOARDS 2012-08-02 04:51:04 10 4 IPHONE CHN 5
2 MOUSE 2012-08-02 04:52:39 20 1 HP UK 1
2 MOUSE 2012-08-02 04:52:39 20 2 DELL USA 1
2 MOUSE 2012-08-02 04:52:39 20 3 ALLEGRO AUS 3
2 MOUSE 2012-08-02 04:52:39 20 4 IPHONE CHN 5
3 CELL PHONE 2012-08-02 04:52:39 30 1 HP UK 1
3 CELL PHONE 2012-08-02 04:52:39 30 2 DELL USA 1
3 CELL PHONE 2012-08-02 04:52:39 30 4 IPHONE CHN 5
4 BOOKS 2012-08-02 04:52:39 40 1 HP UK 1
4 BOOKS 2012-08-02 04:52:39 40 2 DELL USA 1
4 BOOKS 2012-08-02 04:52:39 40 3 ALLEGRO AUS 3
4 BOOKS 2012-08-02 04:52:39 40 4 IPHONE CHN 5
5 MONITOS 2012-08-02 04:52:39 50 1 HP UK 1
5 MONITOS 2012-08-02 04:52:39 50 2 DELL USA 1
5 MONITOS 2012-08-02 04:52:39 50 3 ALLEGRO AUS

5.CROSS JOIN or Cartesian product

============================

This join will produce n1*n2 times of rows. n1-number of rows in first table,n2-number of rows in second table

5.1. SELECT * FROM ORDERS_KARU, CUSTOMERS_KARU;

O_ID O_NAME O_DATE QTY C_ID C_NAME C_ADR O_ID
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK 1
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA 1
1 KEYBOARDS 2012-08-02 04:51:04 10 3 ALLEGRO AUS 3
1 KEYBOARDS 2012-08-02 04:51:04 10 4 IPHONE CHN 5
1 KEYBOARDS 2012-08-02 04:51:04 10 5 NOKIA IND
2 MOUSE 2012-08-02 04:52:39 20 1 HP UK 1
2 MOUSE 2012-08-02 04:52:39 20 2 DELL USA 1
2 MOUSE 2012-08-02 04:52:39 20 3 ALLEGRO AUS 3
2 MOUSE 2012-08-02 04:52:39 20 4 IPHONE CHN 5
2 MOUSE 2012-08-02 04:52:39 20 5 NOKIA IND
3 CELL PHONE 2012-08-02 04:52:39 30 1 HP UK 1
3 CELL PHONE 2012-08-02 04:52:39 30 2 DELL USA 1
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS 3
3 CELL PHONE 2012-08-02 04:52:39 30 4 IPHONE CHN 5
3 CELL PHONE 2012-08-02 04:52:39 30 5 NOKIA IND
4 BOOKS 2012-08-02 04:52:39 40 1 HP UK 1
4 BOOKS 2012-08-02 04:52:39 40 2 DELL USA 1
4 BOOKS 2012-08-02 04:52:39 40 3 ALLEGRO AUS 3
4 BOOKS 2012-08-02 04:52:39 40 4 IPHONE CHN 5
4 BOOKS 2012-08-02 04:52:39 40 5 NOKIA IND
5 MONITOS 2012-08-02 04:52:39 50 1 HP UK 1
5 MONITOS 2012-08-02 04:52:39 50 2 DELL USA 1
5 MONITOS 2012-08-02 04:52:39 50 3 ALLEGRO AUS 3
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN 5
5 MONITOS 2012-08-02 04:52:39 50 5 NOKIA IND

6.Self-join:

=========

Joining the table to itself is self-join

6.1SELECT *

FROM

ORDERS_KARU OK1

JOIN ORDERS_KARU OK2

ON OK1.ORDER_ID =OK2.ORDER_ID;

O_ID O_NAME O_DATE QTY O_ID OR_NAME O_DATE QY
1 KEYBOARDS 2012-08-02 04:51:04 10 1 KEYBOARDS 2012-08-02 04:51:04 10
2 MOUSE 2012-08-02 04:52:39 20 2 MOUSE 2012-08-02 04:52:39 20
3 CELL PHONE 2012-08-02 04:52:39 30 3 CELL PHONE 2012-08-02 04:52:39 30
4 BOOKS 2012-08-02 04:52:39 40 4 BOOKS 2012-08-02 04:52:39 40
5 MONITOS 2012-08-02 04:52:39 50 5 MONITOS 2012-08-02 04:52:39 50
Advertisements
Categories: Technical
%d bloggers like this: