Natural Join, Cross Join and Self Join in SQL [with Examples]

A Join is a powerful tool in SQL for joining multiple tables and extracting data beyond the results returned by simple SQL SELECT using one table. In addition to Inner and Outer Join we have three special Joins called Natural Join, Cross Join and Self Join in SQL.

To understand these three Joins we will use the following tables. SQL for these tables and data is avaible at the end of this article.

Parts

self join in sql example

Sales

self join in SQL table

Natural Join in SQL

A natural join is just like an INNER JOIN in functionality with only difference that the JOIN is created “naturally”. It is automatically done by using the likeliness of columns’ names and data type in the tables to be joined.

Syntax

SELECT column-name1, column-name2….

FROM table1 NATURAL JOIN table2

A natural join will return result rows if table1 and table 2 contain a column name with same name and data type.

Example

Select PartID, PartName and Quantity sold

Query

select partID , partname , Qty from parts Natural JOIN sales ORDER BY partID

Output

PARTID PARTNAME QTY
1 Computer 3
1 Computer 2
4 MotherBoard 1
6 USB 3
9 Stand 3

Cross Join in SQL

A Cross join is a join that doesn’t need a join condition because it is meant to return the rows with all possible combinations of records from tables in the query. A Cross Join is also called a Cartesian Join. If one table has M rows and other table has N rows then a Cross Join returns MXN rows in output.

Syntax

SELECT column-name1, column-name2….

FROM table1 , table2

A Cross Join will return result without any condition matching selected columns from one table with columns belonging to all rows from second table.

Example

Display results of Cross Join between Parts and Sales Tables

Query

select p.partID , partname component, Soldon, Qty from parts p , sales s order by partid

Output

 

PARTID COMPONENT SOLDON QTY
1 Computer 09-JAN-20 3
1 Computer 09-JAN-20 3
1 Computer 09-JAN-20 3
1 Computer 07-JAN-20 2
1 Computer 09-JAN-20 1
2 CPU 09-JAN-20 3
2 CPU 09-JAN-20 1
2 CPU 09-JAN-20 3
2 CPU 09-JAN-20 3
2 CPU 07-JAN-20 2
3 KeyBoard 09-JAN-20 3
3 KeyBoard 09-JAN-20 1
3 KeyBoard 07-JAN-20 2
3 KeyBoard 09-JAN-20 3
3 KeyBoard 09-JAN-20 3
4 MotherBoard 09-JAN-20 3
4 MotherBoard 09-JAN-20 3
4 MotherBoard 07-JAN-20 2
10 Burners 07-JAN-20 2
10 Burners 09-JAN-20 1
10 Burners 09-JAN-20 3
10 Burners 09-JAN-20 3
10 Burners 09-JAN-20 3

Self Join in SQL

A Self Join is a special join in which a table joins itself on two columns of table with different names but storing data from same domain.  The table is defined with two aliases to differentiate as left and right table in the Self Join Query

Syntax

SELECT column-name1, column-name2….

FROM table1 T1, table2 T2

WHERE T1.Col-Name1=T2=Col-Name2

A Self Join will return result after matching the columns specified in the WHERE Clause which belong to the same table.

Example

Display the part id, part name , name of the parent part name and part weight  of all parts

Query

select l.partid, l.partname component, r.partname parentPart, l.partweight from parts l, parts r

where l.partof=r.partid

Output

PARTID COMPONENT PARENTPART PARTWEIGHT
2 CPU Computer 100
3 KeyBoard Computer 200
4 MotherBoard Computer 400
5 Graphics Card Computer 200
6 USB Computer 20
9 Stand Cook Top 500
10 Burners Cook Top 700
8 Nozzle Cook Top 100

Database Creation and Insertion

create table parts
(
partID number(3),
PartName varchar2(40),
PartWeight number(5,2),
PartOf number(3)
)

Insert Into parts values(1,'Computer',300, null);
Insert Into parts values(2,'CPU',100, 1);
Insert Into parts values(3,'KeyBoard',200, 1);
Insert Into parts values(4,'MotherBoard',400, 1);
Insert Into parts values(5,'Graphics Card',200, 1);
Insert Into parts values(6,'USB',20, 1);
Insert Into parts values(7,'Cook Top',700, null);
Insert Into parts values(8,'Nozzle',100, 7);
Insert Into parts values(9,'Stand',500, 7);
Insert Into parts values(10,'Burners',700, 7);

Create Table sales
(
saleID number(6),
partID number(3),
soldOn	date,
Qty	number(3)
)

INSERT INTO sales values (2,1,'07-jan-2020',2);
INSERT INTO sales values (3,4,'09-jan-2020',1);
INSERT INTO sales values (4,6,'09-jan-2020',3);
INSERT INTO sales values (5,9,'09-jan-2020',3);

Read in detail about joins here

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *