SQL Server Joins are used to combine records from two or more tables based on common fields between them.
1. SQL Server INNER JOIN statement
- SQL server INNER JOIN statement selects all possible rows from both tables, if there is a match between the columns in both tables.
- The data is not displayed, if there is no match between columns in both tables.
Syntax
SELECT column_name
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name
INNER JOIN in SQL server.
Consider the following two tables, 'Employee' and 'Salary'.
Table 1: Employee
| Emp_ID | Emp_Name | Emp_Phone |
|---|
| 1 | Marten | 9022222222 |
| 2 | Paul | 9033333333 |
| 3 | Albert | 9044444444 |
| 4 | Steve | 9055555555 |
Table 2: 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 for INNER JOIN 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
| Emp_Name | Transaction_ID | Amount |
|---|
| Marten | 101 | 10000 |
| Paul | 102 | 20000 |
| Albert | 103 | 25000 |
| Steve | 105 | 30000 |
2. SQL Server LEFT JOIN
- SQL Server 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 is NULL.
Syntax
SELECT column_name
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
LEFT JOIN statement in SQL server.
Consider the following two tables 'Employee' and 'Salary'.
Table 1: Employee
| Emp_ID | Emp_Name | Emp_Phone |
|---|
| 1 | Marten | 9022222222 |
| 2 | Paul | 9033333333 |
| 3 | Albert | 9044444444 |
| 4 | Steve | 9055555555 |
Table2: 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. SQL Server RIGHT JOIN
- The SQL server RIGHT JOIN or RIGHT OUTER JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1).
- If there is no match, the result in the left table is null.
Syntax
SELECT Column_name
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2_column_name
RIGHT JOIN Statement in SQL server.
Consider the following two tables titled 'Employee', 'Salary'.
Table: 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 |
Table: '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
| Emp_Name | Amount |
|---|
| Martin | 10000 |
| Paul | 20000 |
| Albert | 25000 |
| Steve | 30000 |
| Smith | 30000 |
| Jhon | |
4. SQL Server FULL JOIN
The SQL server FULL JOIN or FULL 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
FULL JOIN Statement in SQL server.
Consider the following tables
Table 1: Empoyee
| Emp_ID | Emp_Name | Emp_Phone |
|---|
| 1 | Marten | 9022222222 |
| 2 | Paul | 9033333333 |
| 3 | Albert | 9044444444 |
| 4 | Steve | 9055555555 |
| 5 | Smith | 9066666666 |
| 6 | Jhon | 9011111111 |
Table 2: Salary
| Emp_ID | Transaction_ID | Amount |
|---|
| 1 | 101 | 10000 |
| 2 | 102 | 20000 |
| 3 | 103 | 25000 |
| 4 | 104 | 30000 |
To perform RIGHT 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
| 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. SQL Server CROSS JOIN
- The SQL server 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: 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 |
