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

Sales

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

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

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

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

admin