In SQL **joins** are used when we want to select data and information from two or more tables on the basis of some relationships between one or more columns in tables. SQL Joins are used to relate information in different tables. Thus,we can say that SQL Join is used in SQL query to retrieve rows from two or more tables on the basis of some relationship between columns of both the tables. A SQL Join can be used in the SQL WHERE Clause of any SQL statement like select, update, delete statements.
There are different Types of Joins available in SQL :
- Inner Joins
- Cross Joins
- Outer Joins
- Self Joins
Lets understand it with practical example using following tables :
- Create a table UserDetails as given below with UserID as Primary key.
UserID UserName FirstName LastName
1 NishaSharma Nisha Sharma
2 RajKaushik Raj Kaushik
3 DivyaDutta Divya Dutta
Create a table OrderDetails as given below where UserID as Foreign key.
OrderID OrderNo UserID
1 543224 1
2 213424 2
3 977776 3
Inner Joins : Inner join statement returns all the rows in the resulting table which satisfied the condition given in where clause.
Example
SELECT u.UserName ,o.OrderNo
FROM UserDetails u
INNER JOIN OrderDetails o
ON u.UserID=o.UserID
Resulting table for above SQL Query will be as below :
UserName OrderNo
NishaSharma 543224
RajKaushik 213424
DivyaDutta 977776
Cross Join : A cross join produces Cartesian product of the two or more tables as the resulting table. The number of rows in the resulting table with Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.
Example
SELECT * FROM UserDetails
CROSS JOIN OrderDetails
Or
SELECT * FROM UserDetails, OrderDetails
Resulting table for above SQL Query will be as below :
UserID UserName FirstName LastName OrderID OrderNo UserID
1 NishaSharma Nisha Sharma 1 543224 1
1 NishaSharma Nisha Sharma 2 213424 2
1 NishaSharma Nisha Sharma 3 977776 3
2 RajKaushik Raj Kaushik 1 543224 1
2 RajKaushik Raj Kaushik 2 213424 2
2 RajKaushik Raj Kaushik 3 977776 3
3 DivyaDutta Divya Dutta 1 543224 1
3 DivyaDutta Divya Dutta 2 213424 2
3 DivyaDutta Divya Dutta 3 977776 3
Outer Joins : This sql join condition returns a resulting table which contains all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition.
1. Left Outer Join : The left outer join displays all the rows from the Left table and matched rows from the Right table.
Syntax
SELECT ColumnNames FROM tablename1 t1
LEFT OUTER JOIN tablename2 t2
ON t1.columnname=t2.columnname
Example
SELECT u.UserID,u.UserName,o.OrderNo
FROM UserDetails u
LEFT OUTER JOIN OrderDetails o
ON u.UserID=o.UserID
Resulting table for above SQL Query will be as below :
UserID UserName OrderNo
1 NishaSharma 543224
2 RajKaushik 213424
3 DivyaDutta 977776
2. Right Outer Join : The right outer join displays all the rows from the right side table and matched rows from the left table.
Syntax
SELECT ColumnNames
FROM tablename1 t1
RIGHT OUTER JOIN tablename2 t2
ON t1.columnname=t2.columnname
Example
SELECT u.UserID,u.UserName,o.OrderNo
FROM UserDetails u
RIGHT OUTER JOIN OrderDetails o
ON u.UserID=o.UserID
3. Full Outer Join : Full Outer Join displays all the rows of both the tables which satisfies the join condition in where clause as well as the rows which do not satisfies the join condition.
Syntax
SELECT ColumnList
FROM tablename1 t1
FULL OUTER JOIN tablename2 t2
ON t1.columnname=t2.columnname
Example
SELECT u.UserID,u.UserName,o.OrderNo
FROM UserDetails u
FULL OUTER JOIN OrderDetails o
ON u.UserID=o.UserID
4. Self Join : This Sql join is used to join the table to itself. This join can be use when we want to fetch records from a single table on the basis of some relationship between its columns or when we are looking for records with some unique value. In this scenario any of the above joins can be used to join the table to itself depending on the requirement of the user for resulting table.
1 Comment(s)