ORACLE SQL PLSQL JOIN training institutes in Kukatpally KPHB Hyderabad

SQL JOIN

As the name shows, JOIN means to combine something. In case of SQL, JOIN means "to combine two or more tables".

The SQL JOIN clause takes records from two or more tables in a database and combines it together.

ANSI standard SQL defines five types of JOIN :

  1. inner join,
  2. left outer join,
  3. right outer join,
  4. full outer join, and
  5. cross join.

Current Time 2:16
Duration 18:10

Loaded: 18.35%

In the process of joining, rows of both tables are combined in a single table.

Why SQL JOIN is used?

If you want to access more than one table through a select statement.

If you want to combine two or more table then SQL JOIN statement is used .it combines rows of that tables in one table and one can retrieve the information by a SELECT statement.

The joining of two or more tables is based on common field between them.

SQL INNER JOIN also known as simple join is the most common type of join.

How to use SQL join or SQL Inner Join?

Let an example to deploy SQL JOIN process:

1.Staff table

ID Staff_NAME Staff_AGE STAFF_ADDRESS Monthley_Package
1 ARYAN 22 MUMBAI 18000
2 SUSHIL 32 DELHI 20000
3 MONTY 25 MOHALI 22000
4 AMIT 20 ALLAHABAD 12000

2.Payment table

Payment_ID DATE Staff_ID AMOUNT
101 30/12/2009 1 3000.00
102 22/02/2010 3 2500.00
103 23/02/2010 4 3500.00

So if you follow this JOIN statement to join these two tables ?

  1. SELECT Staff_ID, Staff_NAME, Staff_AGE, AMOUNT
  2.    FROM STAFF s, PAYMENT p
  3.    WHERE s.ID =p.STAFF_ID;

This will produce the result like this:

STAFF_ID NAME Staff_AGE AMOUNT
3 MONTY 25 2500
1 ARYAN 22 3000
4 AMIT 25 3500
1 ARYAN 22 3000

 

 

SQL OUTER JOIN

Advertisement
  • In the SQL outer JOIN, all the content from both the tables is integrated together.
  • Even though the records from both the tables are matched or not, the matching and non-matching records from both the tables will be considered an output of the outer join in SQL.
  • There are three different types of outer join in SQL:
    1. Left Outer Join
    2. Right Outer Join
    3. Full Outer Join

Now let us take a deeper dive into the different types of outer join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.

Consider we have the following tables with the given data:

Table 1: employee

EmployeeID Employee_Name Employee_Salary
1 Arun Tiwari 50000
2 Sachin Rathi 64000
3 Harshal Pathak 48000
4 Arjun Kuwar 46000
5 Sarthak Gada 62000
6 Saurabh Sheik 53000
7 Shubham Singh 29000
8 Shivam Dixit 54000
9 Vicky Gujral 39000
10 Vijay Bose 28000

Table 2: department

Advertisement
DepartmentID Department_Name Employee_ID
1 Production 1
2 Sales 3
3 Marketing 4
4 Accounts 5
5 Development 7
6 HR 9
7 Sales 10

Table 3: Loan

LoanID Branch Amount
1 B1 15000
2 B2 10000
3 B3 20000
4 B4 100000
5 B5 150000
6 B6 50000
7 B7 35000
8 B8 85000

Table 4: Borrower

CustID CustName LoanID
1 Sonakshi Dixit 1
2 Shital Garg 4
3 Swara Joshi 5
4 Isha Deshmukh 2
5 Swati Bose 7
6 Asha Kapoor 10
7 Nandini Shah 9

1. Left Outer Join:

  • If we use the left outer join to combine two different tables, then we will get all the records from the left table. But we will get only those records from the right table, which have the corresponding key in the left table.
  • Syntax of writing a query to perform left outer join:

SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 LEFT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;

Example 1:

Write a query to perform left outer join considering employee table as the left table and department table as the right table.

Query:

  1. mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM employee e LEFT OUTER JOIN department d ON e.EmployeeID = d.Employee_ID;

We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the LEFT OUTER JOIN keyword to perform the left outer join operation on the employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables.

You will get the following output:

EmployeeID Employee_Name Employee_Salary DepartmentID Department_Name
1 Arun Tiwari 50000 1 Production
2 Sachin Rathi 64000 NULL NULL
3 Harshal Pathak 48000 2 Sales
4 Arjun Kuwar 46000 3 Marketing
5 Sarthak Gada 62000 4 Accounts
6 Saurabh Sheik 53000 NULL NULL
7 Shubham Singh 29000 5 Development
8 Shivam Dixit 54000 NULL NULL
9 Vicky Gujral 39000 6 HR
10 Vijay Bose 28000 7 Sales

EmployeeID, Employee_Name, Employee_Salary, Department_ID, Department_Name are retrieved from employee and department tables. All the records from the employee table are retrieved. Only those records that have a corresponding EmployeeID in the employee table are retrieved from the department table. Rest other records in the department table for which an employeeID doesn't match with the employeeID of the employee table; then, it is displayed as NULL.

Example 2:

Write a query to perform left outer join considering loan table as the left table and borrower table as the right table.

Query:

  1. mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l LEFT OUTER JOIN Borrower b ON l.LoanID = b.LoanID;

We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the LEFT OUTER JOIN keyword to perform the left outer join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables.

You will get the following output:

LoanID Branch Amount CustID CustName
1 B1 15000 1 Sonakshi Dixit
2 B2 10000 4 Isha Deshmukh
3 B3 20000 NULL NULL
4 B4 100000 2 Shital Garg
5 B5 150000 3 Swara Joshi
6 B6 50000 NULL NULL
7 B7 35000 5 Swati Bose
8 B8 85000 NULL NULL

LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the loan table are retrieved. Only those records that have a corresponding LoanID in the loan table are retrieved from the borrower table. Rest other records in the borrower table for which a LoanID doesn't match with the LoanID of the loan table; are displayed as NULL.

2. Right Outer Join:

  • Right outer join is the reverse of left outer join. If we use the right outer join to combine two different tables, then we will get all the records from the right table. But we will get only those records from the left table, which have the corresponding key in the right table.
  • Syntax of writing a query to perform right outer join:
  1. SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1  RIGHT OUTER JOIN TableName2  ON TableName1.ColumnName = TableName2.ColumnName;

Example 1:

Write a query to perform right outer join considering employee table as the left table and department table as the right table.

Query:

  1. mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM employee e RIGHT OUTER JOIN department d ON e.EmployeeID = d.Employee_ID;

We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the RIGHT OUTER JOIN keyword to perform the right outer join operation on the employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables.

You will get the following output:

EmployeeID Employee_Name Employee_Salary DepartmentID Department_Name
1 Arun Tiwari 50000 1 Production
3 Harshal Pathak 48000 2 Sales
4 Arjun Kuwar 46000 3 Marketing
5 Sarthak Gada 62000 4 Accounts
7 Shubham Singh 29000 5 Development
9 Vicky Gujral 39000 6 HR
10 Vijay Bose 28000 7 Sales

EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name are retrieved from employee and department tables. All the records from the department table are retrieved. Only those records that have a corresponding EmployeeID in the department table are retrieved from the employee table.

Example 2:

Write a query to perform right outer join considering loan table as the left table and borrower table as the right table.

Query:

  1. mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l RIGHT OUTER JOIN Borrower b ON l.LoanID = b.LoanID;

We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the RIGHT OUTER JOIN keyword to perform the right outer join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables.

You will get the following output:

LoanID Branch Amount CustID CustName
1 B1 15000 1 Sonakshi Dixit
4 B4 100000 2 Shital Garg
5 B5 150000 3 Swara Joshi
2 B2 10000 4 Isha Deshmukh
7 B7 35000 5 Swati Bose
NULL NULL NULL 6 Asha Kapoor
NULL NULL NULL 7 Nandini Shah

LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the borrower table are retrieved. Only those records that have a corresponding LoanID in the borrower table are retrieved from the loan table. Rest other records in the loan table for which a LoanID doesn't match with the LoanID of the borrower table; then, are displayed as NULL.

3. Full Outer Join:

  • If we use a full outer join to combine two different tables, then we will get all the records from both the table,e., we will get all the records from the left table as well as the right table.
  • MySQL doesn't support FULL OUTER JOIN directly. So to implement full outer join in MySQL, we will execute two queries in a single query. The first query will be of LEFT OUTER JOIN, and the second query will be of RIGHT OUTER JOIN. We will combine the first and second query with the UNION operator to see the results of FULL OUTER JOIN.
  • Syntax of writing a query to perform full outer join:
  1. SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1  LEFT OUTER JOIN TableName2  ON TableName1.ColumnName = TableName2.ColumnName UNION SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1  RIGHT OUTER JOIN TableName2  ON TableName1.ColumnName = TableName2.ColumnName;

Example 1:

Write a query to perform full outer join considering the employee table as the left table and department table as the right table.

Query:

  1. mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM department d LEFT OUTER JOIN employee e ON e.EmployeeID = d.Employee_ID UNION SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM department d RIGHT OUTER JOIN employee e ON e.EmployeeID = d.Employee_ID;

We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the LEFT OUTER JOIN keyword to perform the left outer join operation on the employee and department table where 'e' and 'd' are aliases. Then we have written a SELECT query to perform right outer join operation on employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables. Both the SELECT queries are combined using the UNION operator.

You will get the following output:

EmployeeID Employee_Name Employee_Salary DepartmentID Department_Name
1 Arun Tiwari 50000 1 Production
3 Harshal Pathak 48000 2 Sales
4 Arjun Kuwar 46000 3 Marketing
5 Sarthak Gada 62000 4 Accounts
7 Shubham Singh 29000 5 Development
9 Vicky Gujral 39000 6 HR
10 Vijay Bose 28000 7 Sales
2 Sachin Rathi 64000 NULL NULL
6 Saurabh Sheik 53000 NULL NULL
8 Shivam Dixit 54000 NULL NULL

EmployeeID, Employee_Name, Employee_Salary, Department_ID, Department_Name are retrieved from employee and department tables. All the records from the employee table are retrieved as a result of the left outer join. Only those records that have a corresponding EmployeeID in the employee table are retrieved from the department table. Rest other records in the department table for which an employeeID doesn't match with the employeeID of the employee table; then, are displayed as NULL. All the records from the department table are retrieved as a result of the right outer join. Only those records that have a corresponding EmployeeID in the department table are retrieved from the employee table.

Example 2:

Write a query to perform full outer join considering loan table as the left table and borrower table as the right table.

Query:

  1. mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l LEFT OUTER JOIN Borrower b ON l.LoanID = b.LoanID UNION SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l RIGHT OUTER JOIN Borrower b ON l.LoanID = b.LoanID;

We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the LEFT OUTER JOIN keyword to perform the left outer join operation on the loan and borrower table where 'l' and 'b' are aliases. Then we have written a SELECT query to perform the right outer join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables. Both the SELECT queries are combined using the UNION operator.

You will get the following output:

LoanID Branch Amount CustID CustName
1 B1 15000 1 Sonakshi Dixit
2 B2 10000 4 Isha Deshmukh
3 B3 20000 NULL NULL
4 B4 100000 2 Shital Garg
5 B5 150000 3 Swara Joshi
6 B6 50000 NULL NULL
7 B7 35000 5 Swati Bose
8 B8 85000 NULL NULL
NULL NULL NULL 6 Asha Kapoor
NULL NULL NULL 7 Nandini Shah

LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the loan table are retrieved as a result of the left outer join. Only those records that have a corresponding LoanID in the loan table are retrieved from the borrower table. Rest other records in the borrower table for which a LoanID doesn't match with the LoanID of the loan table; are displayed as NULL. All the records from the borrower table are retrieved as a result of the right outer join. Only those records that have a corresponding LoanID in the borrower table are retrieved from the loan table. Rest other records in the loan table for which a LoanID doesn't match with the LoanID of the borrower table, are displayed as NULL.

SQL Left Join

Advertisement
Advertisement
  • Join operation in SQL is used to combine multiple tables together into a single table.
  • If we use left join to combine two different tables, then we will get all the records from the left table. But we will get only those records from the right table, which have the corresponding key in the left table. Rest other records in the right table for which the common column value doesn't match with the common column value of the left table; then, it is displayed as NULL.
  • Let us take a look at the syntax of writing a query to perform the left join operation in SQL.
  1. SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 LEFT JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;

Now let us see take a deeper dive into the left join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.

Consider we have the following tables with the given data:

Table 1: employee

EmployeeID Employee_Name Employee_Salary
1 Arun Tiwari 50000
2 Sachin Rathi 64000
3 Harshal Pathak 48000
4 Arjun Kuwar 46000
5 Sarthak Gada 62000
6 Saurabh Sheik 53000
7 Shubham Singh 29000
8 Shivam Dixit 54000
9 Vicky Gujral 39000
10 Vijay Bose 28000

Table 2: department

Advertisement
DepartmentID Department_Name Employee_ID
1 Production 1
2 Sales 3
3 Marketing 4
4 Accounts 5
5 Development 7
6 HR 9
7 Sales 10

Table 3: Loan

LoanID Branch Amount
1 B1 15000
2 B2 10000
3 B3 20000
4 B4 100000
5 B5 150000
6 B6 50000
7 B7 35000
8 B8 85000

Table 4: Borrower

CustID CustName LoanID
1 Sonakshi Dixit 1
2 Shital Garg 4
3 Swara Joshi 5
4 Isha Deshmukh 2
5 Swati Bose 7
6 Asha Kapoor 10
7 Nandini Shah 9

Table 5: customer

Customer_ID Name Age Salary
1 Aryan Jain 51 56000
2 Arohi Dixit 21 25000
3 Vineet Garg 24 31000
4 Ajeet Singh 23 32000
5 Ravi Rathod 23 42000
6 Paras Aggrawal 22 50000
7 Sonakshi Kapadiya 24 28000
8 Sonali Kapoor 28 82000

Table 6: orders

Order_ID Order_Date Cutomer_ID Amount
1 2012-01-20 2 3000
2 2012-05-18 2 2000
3 2012-06-28 3 4000
4 2012-04-11 4 5000
5 2012-05-04 8 8000

Example 1:

Write a query to perform the left join operation considering the employee table as the left table and the department table as the right table.

Query:

Advertisement
  1. mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM employee e LEFT JOIN department d ON e.EmployeeID = d.Employee_ID;

We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the LEFT JOIN keyword to perform the left join operation on the employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables.

You will get the following output:

EmployeeID Employee_Name Employee_Salary DepartmentID Department_Name
1 Arun Tiwari 50000 1 Production
2 Sachin Rathi 64000 NULL NULL
3 Harshal Pathak 48000 2 Sales
4 Arjun Kuwar 46000 3 Marketing
5 Sarthak Gada 62000 4 Accounts
6 Saurabh Sheik 53000 NULL NULL
7 Shubham Singh 29000 5 Development
8 Shivam Dixit 54000 NULL NULL
9 Vicky Gujral 39000 6 HR
10 Vijay Bose 28000 7 Sales

EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name are retrieved from employee and department tables. All the records from the employee table are retrieved. Only those records that have a corresponding EmployeeID in the employee table are retrieved from the department table. Rest other records in the department table for which an EmployeeID doesn't match with the EmployeeID of the employee table; then, it is displayed as NULL.

Example 2:

Write a query to perform the left join operation considering the loan table as the left table and the borrower table as the right table.

Query:

  1. mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l LEFT JOIN Borrower b ON l.LoanID = b.LoanID;

We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the LEFT JOIN keyword to perform the left join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables.

You will get the following output:

Advertisement
LoanID Branch Amount CustID CustName
1 B1 15000 1 Sonakshi Dixit
2 B2 10000 4 Isha Deshmukh
3 B3 20000 NULL NULL
4 B4 100000 2 Shital Garg
5 B5 150000 3 Swara Joshi
6 B6 50000 NULL NULL
7 B7 35000 5 Swati Bose
8 B8 85000 NULL NULL

LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the loan table are retrieved. Only those records that have a corresponding LoanID in the loan table are retrieved from the borrower table. Rest other records in the borrower table for which a LoanID doesn't match with the LoanID of the loan table; then, it is displayed as NULL.

Example 3:

Write a query to perform the left join operation considering the customer table as the left table and the orders table as the right table.

Query:

Advertisement
  1. mysql> SELECT c.Customer_ID, c.Name, c.Age, c.Salary, o.Order_ID, o.Order_Date, o.Amount FROM customer c LEFT JOIN orders o ON c.Customer_ID = o.Customer_ID;

We have used the SELECT command to retrieve Customer_ID, Name, Age, Salary, Order_ID, Order_Date, Amount present in customer and orders table. Then we have used the LEFT JOIN keyword to perform left join operation on the customer and orders table where 'c' and 'o' are aliases. These two tables are joined on the column Customer_ID which is present in both the tables.

You will get the following output:

Customer_ID Name Age Salary Order_ID Order_Date Amount
1 Aryan Jain 51 56000 NULL NULL NULL
2 Arohi Dixit 21 25000 1 2012-01-20 3000
2 Arohi Dixit 21 25000 2 2012-05-18 2000
3 Vineet Garg 24 31000 3 2012-06-28 4000
4 Ajeet Singh 23 32000 4 2012-04-11 5000
5 Ravi Rathod 23 42000 NULL NULL NULL
6 Paras Aggrawal 22 50000 NULL NULL NULL
7 Sonakshi Kapadiya 24 28000 NULL NULL NULL
8 Sonali Kapoor 28 82000 5 2012-05-04 8000

Customer_ID, Name, Age, Salary, Order_ID, Order_Date, Amount are retrieved from customer and orders tables. All the records from the customer table are retrieved. Only those records that have a corresponding Customer_ID in the customer table are retrieved from the orders table.

SQL RIGHT JOIN

  • Join operation in SQL is used to combine multiple tables together into a single table.
  • If we use the right join to combine two different tables, then we will get all the records from the right table. But we will get only those records from the left table, which have the corresponding key in the right table. Rest other records in the left table for which the common column value doesn't match with the common column value of the right table; displayed as NULL.
  • Let us take a look at the syntax of writing a query to perform the right join operation in SQL.
  1. SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1
  2. RIGHT JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;

Now let us see take a deeper dive into the right join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.

Consider we have the following tables with the given data:

Table 1: employee

EmployeeID Employee_Name Employee_Salary
1 Arun Tiwari 50000
2 Sachin Rathi 64000
3 Harshal Pathak 48000
4 Arjun Kuwar 46000
5 Sarthak Gada 62000
6 Saurabh Sheik 53000
7 Shubham Singh 29000
8 Shivam Dixit 54000
9 Vicky Gujral 39000
10 Vijay Bose 28000

Table 2: department

Advertisement
Advertisement
DepartmentID Department_Name Employee_ID
1 Production 1
2 Sales 3
3 Marketing 4
4 Accounts 5
5 Development 7
6 HR 9
7 Sales 10

Table 3: Loan

LoanID Branch Amount
1 B1 15000
2 B2 10000
3 B3 20000
4 B4 100000
5 B5 150000
6 B6 50000
7 B7 35000
8 B8 85000

Table 4: Borrower

CustID CustName LoanID
1 Sonakshi Dixit 1
2 Shital Garg 4
3 Swara Joshi 5
4 Isha Deshmukh 2
5 Swati Bose 7
6 Asha Kapoor 10
7 Nandini Shah 9

Table 5: customer

Customer_ID Name Age Salary
1 Aryan Jain 51 56000
2 Arohi Dixit 21 25000
3 Vineet Garg 24 31000
4 Ajeet Singh 23 32000
5 Ravi Rathod 23 42000
6 Paras Aggrawal 22 50000
7 Sonakshi Kapadiya 24 28000
8 Sonali Kapoor 28 82000

Table 6: orders

Order_ID Order_Date Cutomer_ID Amount
1 2012-01-20 2 3000
2 2012-05-18 2 2000
3 2012-06-28 3 4000
4 2012-04-11 4 5000
5 2012-05-04 8 8000

Example 1:

Write a query to perform a right join operation considering the employee table as the left table and the department table as the right table.

Query:

  1. mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM employee e RIGHT JOIN department d ON e.EmployeeID = d.Employee_ID;

We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the RIGHT JOIN keyword to perform the right join operation on the employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables.

You will get the following output:

EmployeeID Employee_Name Employee_Salary DepartmentID Department_Name
1 Arun Tiwari 50000 1 Production
3 Harshal Pathak 48000 2 Sales
4 Arjun Kuwar 46000 3 Marketing
5 Sarthak Gada 62000 4 Accounts
7 Shubham Singh 29000 5 Development
9 Vicky Gujral 39000 6 HR
10 Vijay Bose 28000 7 Sales

EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name are retrieved from employee and department tables. All the records from the department table are retrieved. Only those records that have a corresponding EmployeeID in the department table are retrieved from the employee table.

Example 2:

Write a query to perform the right join operation considering the loan table as the left table and the borrower table as the right table.

Query:

  1. mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l RIGHT JOIN Borrower b ON l.LoanID = b.LoanID;

We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the RIGHT JOIN keyword to perform the right join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables.

 

You will get the following output:

LoanID Branch Amount CustID CustName
1 B1 15000 1 Sonakshi Dixit
4 B4 100000 2 Shital Garg
5 B5 150000 3 Swara Joshi
2 B2 10000 4 Isha Deshmukh
7 B7 35000 5 Swati Bose
NULL NULL NULL 6 Asha Kapoor
NULL NULL NULL 7 Nandini Shah

LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the borrower table are retrieved. Only those records that have a corresponding LoanID in the borrower table are retrieved from the loan table. Rest other records in the loan table for which a LoanID doesn't match with the LoanID of the borrower table, are displayed as NULL.

Example 3:

Advertisement

Write a query to perform a right join operation considering the customer table as the left table and the orders table as the right table.

Query:

  1. mysql> SELECT c.Customer_ID, c.Name, c.Age, c.Salary, o.Order_ID, o.Order_Date, o.Amount FROM customer c RIGHT JOIN orders o ON c.Customer_ID = o.Customer_ID;

We have used the SELECT command to retrieve Customer_ID, Name, Age, Salary, Order_ID, Order_Date, Amount present in customer and orders table. Then we have used the RIGHT JOIN keyword to perform the right join operation on the customer and orders table where 'c' and 'o' are aliases. These two tables are joined on the column Customer_ID which is present in both the tables.

You will get the following output:

Customer_ 

ID

 Name Age Salary Order_ID Order_Date Amount
2 Arohi Dixit 21 25000 1 2012-01-20 3000
2 Arohi Dixit 21 25000 2 2012-05-18 2000
3 Vineet Garg 24 31000 3 2012-06-28 4000
4 Ajeet Singh 23 32000 4 2012-04-11 5000
8 Sonali Kapoor 28 82000 5 2012-05-04 8000

Customer_ID, Name, Age, Salary, Order_ID, Order_Date, Amount are retrieved from customer and orders tables. All the records from the orders table are retrieved. From the customer table, only those records which have a corresponding Customer_ID in the orders table are retrieved.

SQL FULL JOIN

The SQL full join is the result of combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found.

SQL full outer join and SQL join are same. generally it is known as SQL FULL JOIN.

SQL full outer join:

What is SQL full outer join?

SQL full outer join is used to combine the result of both left and right outer join and returns all rows (don't care its matched or unmatched) from the both participating tables.

Syntax for full outer join:

  1. SELECT *
  2. FROM table1
  3. FULL OUTER JOIN table2
  4. ON table1.column_name = table2.column_name;

Note:here table1 and table2 are the name of the tables participating in joining and column_name is the column of the participating tables.

Let us take two tables to demonstrate full outer join:

table_A

A M
1 m
2 n
4 o

table_B

A N
2 p
3 q
5 r

Resulting table

A M A N
2 n 2 p
1 m - -
4 o - -
- - 3 q
- - 5 r

Because this is a full outer join so all rows (both matching and non-matching) from both tables are included in the output. Here only one row of output displays values in all columns because there is only one match between table_A and table_B.

Pictorial representation of full outer join:

 

 

SQL Cross Join

  • Join operation in SQL is used to combine multiple tables together into a single table.
  • If we use the cross join to combine two different tables, then we will get the Cartesian product of the sets of rows from the joined table. When each row of the first table is combined with each row from the second table, it is known as Cartesian join or cross join.
  • After performing the cross join operation, the total number of rows present in the final table will be equal to the product of the number of rows present in table 1 and the number of rows present in table 2.
  • For example:
    If there are two records in table 1 and three records in table 2, then after performing cross join operation, we will get six records in the final table.
  • Let us take a look at the syntax of writing a query to perform the cross join operation in SQL.
  1. SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 CROSS JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;

Now let us see take a deeper dive into the cross join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.

Consider we have the following tables with the given data:

Table 1: MatchScore

Player Department_id Goals
Franklin 1 2
Alan 1 3
Priyanka 2 2
Rajesh 3 5

Table 2: Departments

Advertisement
Department_id Department_name
1 IT
2 HR
3 Marketing

Table 3: employee

EmployeeID Employee_Name Employee_Salary
1 Arun Tiwari 50000
2 Sachin Rathi 64000
3 Harshal Pathak 48000
4 Arjun Kuwar 46000
5 Sarthak Gada 62000

Table 4: department

DepartmentID Department_Name Employee_ID
1 Production 1
2 Sales 3
3 Marketing 4
4 Accounts 5

Table 5: loan

LoanID Branch Amount
1 B1 15000
2 B2 10000
3 B3 20000
4 B4 100000

Table 6: borrower

CustID CustName LoanID
1 Sonakshi Dixit 1
2 Shital Garg 4
3 Swara Joshi 5
4 Isha Deshmukh 2

Table 7: customer

Customer_ID Name Age Salary
1 Aryan Jain 51 56000
2 Arohi Dixit 21 25000
3 Vineet Garg 24 31000

Table 8: orders

Order_ID Order_Date Cutomer_ID Amount
1 2012-01-20 2 3000
2 2012-05-18 2 2000
3 2012-06-28 3 4000

Example 1:

Write a query to perform the cross join operation considering the MatchScore table as the left table and the Departments table as the right table.

Query:

  1. SELECT * FROM MatchScore CROSS JOIN Departments;

We have used the SELECT command with the asterisk to retrieve all the columns present in the MatchScore and Departments table. Then we have used the CROSS JOIN keyword to perform the cross join operation on the MatchScore and Departments table. Since there are 4 records in the MatchScore and 3 records in the Departments table, after performing the cross join operation, we will get 12 rows.

After executing this query, you will find the following result:

Player Department_id Goals Depatment_id Department_name
Franklin 1 2 1 IT
Alan 1 3 1 IT
Priyanka 2 2 1 IT
Rajesh 3 5 1 IT
Franklin 1 2 2 HR
Alan 1 3 2 HR
Priyanka 2 2 2 HR
Rajesh 3 5 2 HR
Franklin 1 2 3 Marketing
Alan 1 3 3 Marketing
Priyanka 2 2 3 Marketing
Rajesh 3 5 3 Marketing

Each row from the MatchScore table is combined with each row of the Departments table. Since there are four records in the MatchScore and three records in the Departments table, we have got 12 rows in the final table after performing the cross join operation.

Example 2:

Write a query to perform the cross join operation considering the employee table as the left table and the department table as the right table.

Query:

  1. mysql> SELECT *FROM employee CROSS JOIN department;

We have used the SELECT command with the asterisk to retrieve all the columns present in the employee and department table. Then we have used the CROSS JOIN keyword to perform the cross join operation on the employee and department table. Since there are five records in the employee and four records in the department table, after performing the cross join operation, we will get 20 rows.

After executing this query, you will find the following result:

EmployeeID Employee_Name Employee_Salary DepartmentID Department_Name Employee_ID
1 Arun Tiwari 50000 1 Production 1
1 Arun Tiwari 50000 2 Sales 3
1 Arun Tiwari 50000 3 Marketing 4
1 Arun Tiwari 50000 4 Accounts 5
2 Sachin Rathi 64000 1 Production 1
2 Sachin Rathi 64000 2 Sales 3
2 Sachin Rathi 64000 3 Marketing 4
2 Sachin Rathi 64000 4 Accounts 5
3 Harshal Pathak 48000 1 Production 1
3 Harshal Pathak 48000 2 Sales 3
3 Harshal Pathak 48000 3 Marketing 4
3 Harshal Pathak 48000 4 Accounts 5
4 Arjun Kuwar 46000 1 Production 1
4 Arjun Kuwar 46000 2 Sales 3
4 Arjun Kuwar 46000 3 Marketing 4
4 Arjun Kuwar 46000 4 Accounts 5
5 Sarthak Gada 62000 1 Production 1
5 Sarthak Gada 62000 2 Sales 3
5 Sarthak Gada 62000 3 Marketing 4
5 Sarthak Gada 62000 4 Accounts 5

Each row from the employee table is combined with each row of the department table. Since there are five records in the employee table and four records in the department table, we have got 20 rows in the final table after performing the cross join operation.

Example 3:

Write a query to perform the cross join operation considering the loan table as the left table and the borrower table as the right table.

Query:

  1. mysql> SELECT *FROM loan CROSS JOIN borrower;

We have used the SELECT command with the asterisk to retrieve all the columns present in the loan and the borrower table. Then we have used the CROSS JOIN keyword to perform the cross join operation on the loan and the borrower table. Since there are four records in the loan table and four records in the borrower table, after performing the cross join operation, we will get 16 rows.

After executing this query, you will find the following result:

LoanID Branch Amount CustID CustName LoanID
1 B1 15000 1 Sonakshi Dixit 1
2 B2 10000 1 Sonakshi Dixit 1
3 B3 20000 1 Sonakshi Dixit 1
4 B4 100000 1 Sonakshi Dixit 1
1 B1 15000 2 Shital Garg 4
2 B2 10000 2 Shital Garg 4
3 B3 20000 2 Shital Garg 4
4 B4 100000 2 Shital Garg 4
1 B1 15000 3 Swara Joshi 5
2 B2 10000 3 Swara Joshi 5
3 B3 20000 3 Swara Joshi 5
4 B4 100000 3 Swara Joshi 5
1 B1 15000 4 Isha Deshmukh 2
2 B2 10000 4 Isha Deshmukh 2
3 B3 20000 4 Isha Deshmukh 2
4 B4 100000 4 Isha Deshmukh 2

Each row from the loan table is combined with each row of the borrower table. Since there are four records in the loan table and four records in the borrower table, after performing the cross join operation, we have got 16 rows.

Example 4:

Write a query to perform the cross join operation considering the customer table as the left table and the orders table as the right table.

Query:

  1. mysql> SELECT *FROM customer CROSS JOIN orders;

We have used the SELECT command with the asterisk to retrieve all the columns present in the customer and orders table. Then we have used the CROSS JOIN keyword to perform the cross join operation on the customer table and the orders table. Since there are three records in the loan table and three records in the orders table, after performing the cross join operation, we will get 9 rows.

After executing this query, you will find the following result:

Customer_ID Name Age Salary Order_ID Order_Date Customer_ID Amount
1 Aryan Jain 51 56000 1 2012-01-20 2 3000
2 Arohi Dixit 21 25000 1 2012-01-20 2 3000
3 Vineet Garg 24 31000 1 2012-01-20 2 3000
1 Aryan Jain 51 56000 2 2012-05-18 2 2000
2 Arohi Dixit 21 25000 2 2012-05-18 2 2000
3 Vineet Garg 24 31000 2 2012-05-18 2 2000
1 Aryan Jain 51 56000 3 2012-06-28 3 4000
2 Arohi Dixit 21 25000 3 2012-06-28 3 4000
3 Vineet Garg 24 31000 3 2012-06-28 3 4000

Each row from the customers' table is combined with each row of the orders table. Since there are three records in the loan table and three records in the orders table, after performing the cross join operation, we will get 9 rows.