SQL Server Joins: INNER, OUTER, CROSS

Inner Join

Inner Join is one of the most commonly used joins in SQL Server. It is used to combine rows from two or more tables based on a related column between them. Only the matching rows will be returned by Inner Join. It can help to eliminate redundant data in the output.

To perform an Inner Join, you need to specify the common column between the tables that you would like to join. The syntax is as follows:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Suppose we have two tables - ‘employees’ and ‘departments’. The ‘employees’ table contains the data of all employees in a company, and the ‘departments’ table contains the data of all the departments in which these employees work. Both tables have a common column, ‘dept_id’, which can be used to join the tables. We can use the Inner Join to fetch the list of employees who belong to a particular department along with the details of the department.

SELECT employees.emp_name, departments.dept_name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.dept_id
WHERE departments.dept_name = 'Marketing';

In the above example, we have used Inner Join to fetch the names of all employees working in the ‘Marketing’ department. The output will only include the rows where there is a match between the ‘dept_id’ column in both tables.

Inner Join is powerful and efficient when it comes to fetching data from multiple tables. It helps to combine the data from different tables that share a common column.

Left and Right Outer Join

Left and Right Outer Join are two types of Outer Joins in SQL Server. Both join types are used to combine rows from multiple tables based on a related column between them. The main difference between Left and Right Outer Join is that Left Outer Join returns all the rows from the left table and matching rows from the right table while Right Outer Join returns all the rows from the right table and matching rows from the left table.

Let’s consider an example where we have two tables - ‘employees’ and ‘salary’. The ‘employees’ table contains the data of all employees in a company, and the ‘salary’ table contains the data of each employee’s salary. Both tables have a common column, ‘emp_id’, which can be used to join the tables.

SELECT *
FROM employees
LEFT OUTER JOIN salary
ON employees.emp_id = salary.emp_id;

In the above example, we are using a Left Outer Join to fetch all the records from the ‘employees’ table and only the matching records from the ‘salary’ table. If there is no matching record in the ‘salary’ table, then the values returned for the matching columns will be NULL.

SELECT *
FROM employees
RIGHT OUTER JOIN salary
ON employees.emp_id = salary.emp_id;

In the above example, we are using a Right Outer Join to fetch all the records from the ‘salary’ table and only the matching records from the ‘employees’ table. If there is no matching record in the ‘employees’ table, then the values returned for the matching columns will be NULL.

Both Left and Right Outer Joins are useful for fetching data from multiple tables where you want to include all the rows from one table, even if there is no matching row in the other table. They are powerful tools for data analysis and reporting.

Full Outer Join

Full Outer Join is a type of Join in SQL Server that returns all the rows from both tables, including matched and unmatched rows. It is also known as a Full Join or a Full Outer Join. Full Outer Join combines the results of Left and Right Outer Joins.

Let’s consider an example where we have two tables - ‘employees’ and ‘departments’. The ‘employees’ table contains the data of all employees in a company, and the ‘departments’ table contains the data of all the departments in which these employees work. Both tables have a common column, ‘dept_id’, which can be used to join the tables.

SELECT *
FROM employees
FULL OUTER JOIN departments
ON employees.dept_id = departments.dept_id;

In the above example, we are using Full Outer Join to fetch all the records from both the ‘employees’ and ‘departments’ tables. The ‘JOIN’ keyword is used to match the rows from both tables based on the ‘dept_id’ column.

Full Outer Join returns all the rows from both tables, even if there is no matching record in either table. If there is no matching record in the ‘employees’ table, then the values returned for the employee columns will be NULL, and if there is no matching record in the ‘departments’ table, then the values returned for the department columns will be NULL.

Full Outer Join is a powerful tool for fetching data from multiple tables where you want to include all the rows from both tables, whether they match or not. It is useful for data analysis and reporting, and helps to identify the unmatched rows in each table.

Cross Join

Cross Join, also known as Cartesian Join, is a type of Join in SQL Server that returns the Cartesian product of the two tables being joined. In other words, it creates a combination of all rows from the first table with all rows from the second table.

Let’s consider an example where we have two tables - ‘employees’ and ‘departments’. The ‘employees’ table contains the data of all employees in a company, and the ‘departments’ table contains the data of all the departments in which these employees work. If we use Cross Join on these two tables, it will create a record for each combination of employee and department.

SELECT employees.emp_name, departments.dept_name
FROM employees
CROSS JOIN departments;

In the above example, we are using Cross Join to fetch all possible combinations of employees and departments. The output will contain all the employee names repeated for each department name and all the department names repeated for each employee name.

Cross Join is not used very often in real-world scenarios. It can be useful in situations where you need to generate test data or for generating reports. It is important to use Cross Join sparingly as it can return very large result sets and take up a lot of system resources.

Summary

SQL Server Joins - INNER, OUTER, CROSS - are an essential part of SQL programming. Inner Join combines data from two tables based on a common column, Left and Right Outer Join returns data from one table and matching/null data from the other, Full Outer Join combines data from both tables irrespective of whether it has a match or not, and Cross Join returns a combination of all rows from the first table with all rows from the second table. Understanding SQL Joins is crucial for developers working with data from multiple tables.