SQL Joins
Introduction
SQL Joins are used to combine records from two or more tables based on common fields between them.
The five types of Joins are:1. SQL INNER JOIN Statement
- SQL INNER JOIN statement selects all possible rows from both tables, if there is a match between the columns in both tables.
- The data should not be displayed, if there is no match between columns in both columns.
Syntax:
SELECT column_name
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Example : Query using INNER JOIN Statement.
Consider the following table titled 'Employee'.
| Emp_ID | Emp_Name | Emp_Phone |
|---|
| 1 | Marten | 9022222222 |
| 2 | Paul | 9033333333 |
| 3 | Albert | 9044444444 |
| 4 | Steve | 9055555555 |
Consider the following table titled 'Salary'.
| Emp_ID | Transaction_ID | Amount |
|---|
| 1 | 101 | 10000 |
| 2 | 102 | 20000 |
| 3 | 103 | 25000 |
| 4 | 104 | 30000 |
| 5 | 105 | 30000 |
| 6 | 106 | 50000 |
The column 'Emp_ID' is common to both tables. So the query should be written as:
SELECT Employee.Emp_Name, Salary.Transaction_ID, Salary.Amount
FROM Employee
INNER JOIN Salary
ON Salary.Emp_ID = Employee.EMP_ID;
The result is shown in the following table.
| Emp_Name | Transaction_ID | Amount |
|---|
| Marten | 101 | 10000 |
| Paul | 102 | 20000 |
| Albert | 103 | 25000 |
| Steve | 105 | 30000 |
2. LEFT JOIN
- SQL LEFT JOIN or LEFT OUTER JOIN keyword returns all rows from the left table (table1) with the matching rows in the right table (table2).
- If there is no match found, the result displayed as NULL if no match found on right column.
Syntax:
SELECT column_name
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example : Query using LEFT JOIN statement.
Consider the following table titled 'Employee'.
| Emp_ID | Emp_Name | Emp_Phone |
|---|
| 1 | Marten | 9022222222 |
| 2 | Paul | 9033333333 |
| 3 | Albert | 9044444444 |
| 4 | Steve | 9055555555 |
Consider the following table titled 'Salary'.
| Emp_ID | Transaction_ID | Amount |
|---|
| 1 | 101 | 10000 |
| 2 | 102 | 20000 |
| 3 | 103 | 25000 |
| 4 | 104 | 30000 |
| 5 | 105 | 30000 |
| 6 | 106 | 50000 |
To perform LEFT JOIN on table (Salary), query should be written as:
SELECT Employee.Emp_Name, Employee.Emp_Phone, Salary.Amount
FROM Employee
LEFT JOIN Salary
ON Salary.Emp_ID = Employee.Emp_ID;
| Emp_Name | Emp_Phone | Amount |
|---|
| Marten | 9022222222 | 10000 |
| Paul | 9033333333 | 20000 |
| Albert | 9044444444 | 25000 |
| Steve | 9055555555 | 30000 |
3. RIGHT JOIN
- The SQL RIGHT JOIN or RIGHT OUTER JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1).
- The result is null in the left table, if there is no match.
Syntax:
SELECT Column_name
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Example : Query using RIGHT JOIN Statement.
Consider the following table entitled 'Employee'.
| Emp_ID | Emp_Name | Emp_Phone |
|---|
| 1 | Marten | 9022222222 |
| 2 | Paul | 9033333333 |
| 3 | Albert | 9044444444 |
| 4 | Steve | 9055555555 |
| 5 | Smith | 9066666666 |
| 6 | Jhon | 9011111111 |
Consider the following table entitled as 'Salary'.
| Emp_ID | Transaction_ID | Amount |
|---|
| 1 | 101 | 10000 |
| 2 | 102 | 20000 |
| 3 | 103 | 25000 |
| 4 | 104 | 30000 |
| 5 | 105 | 30000 |
To perform RIGHT JOIN ON table (Employee), query should be written as:
SELECT Employee.Emp_Name, Salary.Amount
FROM Salary
RIGHT JOIN Employee
ON Salary.Emp_ID = Employee.Emp_ID;
The result is shown in the following table.
| Emp_Name | Amount |
|---|
| Martin | 10000 |
| Paul | 20000 |
| Albert | 25000 |
| Steve | 30000 |
| Smith | 30000 |
| Jhon | |
4. SQL FULL JOIN
The FULL JOIN or FULL OUTER JOIN statement returns all rows from the left table (table1) and the right table (table2).
Syntax:
SELECT column_name
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
Example : Query using FULL JOIN Statement.
Consider the following table titled 'Employee'.
| Emp_ID | Emp_Name | Emp_Phone |
|---|
| 1 | Marten | 9022222222 |
| 2 | Paul | 9033333333 |
| 3 | Albert | 9044444444 |
| 4 | Steve | 9055555555 |
| 5 | Smith | 9066666666 |
| 6 | Jhon | 9011111111 |
Consider the following table entitled 'Salary'.
| Emp_ID | Transaction_ID | Amount |
|---|
| 1 | 101 | 10000 |
| 2 | 102 | 20000 |
| 3 | 103 | 25000 |
| 4 | 104 | 30000 |
To perform FULL JOIN, query should be written as:
SELECT Employee.Emp_ID, Employee.Emp_Name, Salary.Transaction_ID, Salary.Amount
FROM Employee
FULL JOIN Salary
ON Salary.Emp_ID = Employee.Emp_ID;
The result is shown in the following table.
| Emp_ID | Emp_Name | Transaction_ID | Amount |
|---|
| 1 | Marten | 101 | 10000 |
| 2 | Paul | 102 | 20000 |
| 3 | Albert | 103 | 25000 |
| 4 | Steve | 104 | 30000 |
| 5 | Smith | | |
| 6 | Jhon | | |
5. CROSS JOIN
- The SQL CROSS JOIN returns the cartesian product of rows from joined table.
- A cartesian join is a join of all rows of table1 to all rows of table2.
Syntax:
SELECT * FROM table1 CROSS JOIN table2;
Example : Query using CROSS JOIN Statement.
Write a query to perform cross join on table1 and table2
Table1 : Students
| Stud_ID | Stud_Name | Subject | Dept_ID |
|---|
| 1 | Ravi | C | 01 |
| 2 | Sapna | CPP | 02 |
| 3 | Prajakta | JAVA | 03 |
Table2 : Department
| Dept_ID | Dept_Name |
|---|
| 01 | Comp_Science |
| 02 | IT |
| 03 | MCA |
SELECT Std_Id, Stud_Name, Subject, Dept_Name
FROM students CROSS JOIN Department;
| Stud_ID | Stud_Name | Subject | Dept_Name |
|---|
| 1 | Ravi | C | Comp_Science |
| 2 | Sapna | CPP | Comp_Science |
| 3 | Prajakta | JAVA | Comp_Science |
| 1 | Ravi | C | IT |
| 2 | Sapna | CPP | IT |
| 3 | Prajakta | JAVA | IT |
| 1 | Ravi | C | MCA |
| 2 | Sapna | CPP | MCA |
| 3 | Prajakta | JAVA | MCA |
