SQL Joins

There are mainly 3 types of joins.

• INNER
• OUTER
• CROSS

·  INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

Example SQL statement:

SELECT * FROM Individual AS Ind

INNER JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId


LEFT= Individual             RIGHT- Publisher

Individual

ID	First Name	Last Name	User Name
1	Kamal	Perera	K
2	Nimal	Silve	N
3	Saman	Peries	S
4	Amal	Fernando	A
5	Bimal	Bandara	B

Publisher

Individual ID	Acces Level
1	Admin
2	Contri
3	Contri
4	Contri
10	Admin

Result

Individual ID	First	Last	User	AID	Access Level
1	Kamal	Perera	K	1	Admin
2	Nimal	Silve	N	2	Contri
3	Saman	Peries	S	3	Contri
4	Amal	Fernando	A	4	Contri

·       OUTER JOIN is of 3 types

1. LEFT OUTER JOIN

Use this when you only want to return rows that have matching data in the left table, even if there are no matching rows in the right table.

Example SQL statement:

LEFT= Individual       RIGHT- Publisher

SELECT * FROM Individual AS Ind

LEFT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Individual

ID	First Name	Last Name	User Name
1	Kamal	Perera	K
2	Nimal	Silve	N
3	Saman	Peries	S
4	Amal	Fernando	A
5	Bimal	Bandara	B

  

Publisher

Individual ID	Acces Level
1	Admin
2	Contri
3	Contri
4	Contri
10	Admin

Result

Individual ID	First	Last	User	AID	Access Level
1	Kamal	Perera	K	1	Admin
2	Nimal	Silve	N	2	Contri
3	Saman	Peries	S	3	Contri
4	Amal	Fernando	A	4	Contri
5	Bimal	Bandara	B	NULL	NULL

2. RIGHT OUTER JOIN

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Example SQL statement:

LEFT= Individual        RIGHT- Publisher

SELECT * FROM Individual AS Ind

RIGHT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Individual

ID	First Name	Last Name	User Name
1	Kamal	Perera	K
2	Nimal	Silve	N
3	Saman	Peries	S
4	Amal	Fernando	A
5	Bimal	Bandara	B

  

Publisher

Individual ID	Acces Level
1	Admin
2	Contri
3	Contri
4	Contri
10	Admin

Result

Individual ID	First	Last	User	AID	Access Level
1	Kamal	Perera	K	1	Admin
2	Nimal	Silve	N	2	Contri
3	Saman	Peries	S	3	Contri
4	Amal	Fernando	A	4	Contri
NULL	NULL	NULL	NULL	10	Admin

3.FULL OUTER JOIN

Use this when you want to all rows, even if there are no matching rows in the right table.

Example SQL statement:

LEFT= Individual        RIGHT- Publisher

SELECT * FROM Individual AS Ind

FULL JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Individual

ID	First Name	Last Name	User Name
1	Kamal	Perera	K
2	Nimal	Silve	N
3	Saman	Peries	S
4	Amal	Fernando	A
5	Bimal	Bandara	B

  

Publisher

Individual ID	Acces Level
1	Admin
2	Contri
3	Contri
4	Contri
10	Admin

Result

Individual ID	First	Last	User	AID	Access Level
1	Kamal	Perera	K	1	Admin
2	Nimal	Silve	N	2	Contri
3	Saman	Peries	S	3	Contri
4	Amal	Fernando	A	4	Contri
5	Bimal	Bandara	B	NULL	NULL
NULL	NULL	NULL	NULL	10	Admin

Note

• A OUTER join has to be LEFT | RIGHT | FULL you cannot simply say OUTER JOIN 
• You can drop OUTER keyword and just say LEFT JOIN or RIGHT JOIN or FULL JOIN
• If you just mention JOIN then by default it is a INNER JOIN 

 ·       CROSS JOIN

• The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table.
• If ‘WHERE’ clause is not used along with CROSS JOIN, This kind of result is called as Cartesian product.
• If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

Example SQL statement:

Select * from Individual

Cross JOIN Publisher

Thanks & Cheers :) 

Thilini