SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
The process of forming pairs of rows by matching the contents of related columns is called joining the tables. The resulting table (containing data from both of the original tables) is called a join between the two table.
Join are the only mechanism for exercising cross-table data relationships. A typical join condition specifies a foreign key from one table and its associated key in the other table. Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.
Different types of  SQL  join
- JOIN: Return rows when there is at least one match in both tables.
- INNER JOIN: Return rows when there is at least one match in both tables.
- LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table.
- RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.
- FULL JOIN: Return rows when there is a match in one of the tables.
Before we see the join example, first will create two tables. Â They are :
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
….
)
We create a first table called “Persons” that contains five columns: P_Id, LastName, FirstName, Address, and City.
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Persons Table
P_Id | FirstName | LastName | Address | city |
We will store the Persons details in the Persons table like P_Id, lastName and address. In the Persons table P_Id column is the identity column. Hence P_Id is the Primary key.
INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)
Now we  insert a values  in row in the “Persons” table.
INSERT INTO Persons
VALUES (4,’Nilsen’, ‘Pettersen’Â , ‘Stavanger’, ‘ Hansen’)
Persons Table
P_Id | FirstName | LastName | Address | city |
1 | Hansen | ola | Timoteivn | Sandnes |
2 | Stavanger | toa | Borgvn | Sandnes |
3 | Pettersen | Kari | Storgt | Stavanger |
4 | Nilsen | johan | Bakken | Stavanger |
We will create second Order table, and in the Orders table, order information will be stored. In the order table CompanyId will have the value of the id column of the company table. Hence P_Id is the foreign key.
Order Table
O_Id | Order_no | Address | P_Id |
1 | 323 5 | Borgvn | 3 |
2 | 8876 | Borgvn | 3 |
3 | 6677 | Storgt | 2 |
4 | 9065 | Timoteivn | 1 |
5 | 1145 | Timosion | 22 |
INNER JOIN or JOIN
The INNER JOIN key word will return there is at least one match in both tables.
Syntax
SELECT colume_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
or
SELECT colume_name(s)
FROM table_name1
JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example
SELECT Persons. FirstName, Persons. LastName, Order. Order_no
FROM Persons
INNER JOIN Order
ON Persons.P_Id=Order.P_Id
Order By Person. LastName
Result
LastName | FirstName | Order_no |
ola | Hansen | 9065 |
toa | Stavanger | 6677 |
kari | Pettersen | 8876 |
kari | Pettersen | 3235 |
The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in “Persons” that do not have matches in “Orders“, those rows will NOT be listed.
LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
Syntex
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example
SELECT Persons. FirstName, Persons. LastName, Order. Order_no
FROM Persons
LEFT JOIN Order
ON Persons.P_Id=Order.P_Id
Order By Person. LastName
Result
LastName | FirstName | Order_no |
ola | Hansen | 9065 |
toa | Stavanger | 6677 |
kari | Pettersen | 8876 |
kari | Pettersen | 3235 |
johan | Nilsen | NULL |
In the Left Join example result “Nilsen” details included even though there is no “Order_no” Â available in the orders table.
RIGHT JOIN
The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
Syntex
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example
SELECT Persons. FirstName, Persons. LastName, Order. Order_no
FROM Persons
RIGHT JOIN Order
ON Persons.P_Id=Order.P_Id
Order By Person. LastName
It will return all the rows from the right table (Order) even if there are no rows in the left table (Persons)
In the orders table we will not have the orders for Nilsen. When you use the left join Nilsen will  be included in the list. But if we use right join then they will not  be included.
Result
LastName | FirstName | Order_no |
ola | Hansen | 9065 |
toa | Stavanger | 6677 |
kari | Pettersen | 8876 |
kari | Pettersen | 3235 |
NULL | NULL | 1145 |
The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).
FULL JOIN
The FULL JOIN keyword return rows when there is a match in one of the tables.
Syntex
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example
SELECT Persons. FirstName, Persons. LastName, Order. Order_no
FROM Persons
FULL JOIN Order
ON Persons.P_Id=Order.P_Id
Order By Person. LastName
FULL JOIN will return all the rows from the left table Persons and all the rows from the right table Orders. If there are rows in Orders that do not have matches in Persons, or if there are rows in Persons that do not have matches in Orders, those rows will be listed as well.
RESULT
LastName | FirstName | Order_no |
ola | Hansen | 9065 |
toa | Stavanger | 6677 |
kari | Pettersen | 8876 |
kari | Pettersen | 3235 |
johan | Nilsen | NULL |
NULL | NULL | 1145 |
In the persons table we are not having persons last and firstname for some orders. For that case, Null values is included in the LastName and FirstName columns. Like this same in order_no column, Null values is included.  As a result, all of the rows from the Orders table, and all the rows from persons table displayed.