What is Join in SQL Server and how many types of Joins in SQLServer?

Asked 14-Sep-2021
Updated 06-Apr-2023
Viewed 486 times

0

What is Join in SQL Server and how many types of Joins in SQLServer? Why are use the Join in Database?


2 Answers


0

In SQL Server, a JOIN operation combines rows from two or more tables based on a related column between them. A JOIN clause is used to specify the relationship between the tables being joined.

There are four main types of JOINs in SQL Server:

  • Inner Join: Returns only the rows that have matching values in both tables being joined.
  • Left Outer Join: Returns all the rows from the left table and matching rows from the right table. If there is no matching row in the right table, the result will contain NULL values.
  • Right Outer Join: Returns all the rows from the right table and matching rows from the left table. If there is no matching row in the left table, the result will contain NULL values.
  • Full Outer Join: Returns all the rows from both tables, including any rows that do not have matching values in the other table. If there is no matching row in one of the tables, the result will contain NULL values.

Each type of JOIN has its own syntax and usage, and it's important to choose the right type of JOIN for the specific task at hand.

Suppose you have two tables: "employees" and "departments". The "employees" table has columns for employee ID, name, and department ID. The "departments" table has columns for department ID and department name.

To join these two tables based on the department ID column, you can use an INNER JOIN like this:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

This SQL query will return a result set that includes the names of all employees and their corresponding department names. The INNER JOIN operation ensures that only the rows that have matching department IDs in both tables are included in the result set.

You can use other types of JOINs in a similar manner, by replacing "INNER JOIN" with "LEFT OUTER JOIN", "RIGHT OUTER JOIN", or "FULL OUTER JOIN" depending on the specific requirements of your query.


0

SQL JOIN Command :
 The SQL JOIN query is used to combine two or more tables based on common column name to retrieve record from all that tables.
Types of SQL JOIN Command :
 There are 4 types of JOIN Command are used in SQL Server.
1- Inner join.
2- Left join.
3- Right join.
4- Full outer join.
Inner join
 In SQL Inner join there are common record from all table is retrieved based on condition
                                What is Join in SQL Server and how many types of Joins in SQLServer?
Left join :
In SQL in Left join all the record from Left table is selected and the common record from Right table are selected base on condition.
                            What is Join in SQL Server and how many types of Joins in SQLServer?
Right join :
 In SQL Server in Right join all the record from right table and common record of left table is execute.
                              What is Join in SQL Server and how many types of Joins in SQLServer?
Full outer join :
 Full outer join retrieve all record from each tables which are connected with SQL statement.
                                What is Join in SQL Server and how many types of Joins in SQLServer?
Example : 
There are two tables ‘Customer’ and ‘Salesman’ are as-
 Customer :            What is Join in SQL Server and how many types of Joins in SQLServer?

Salesman :            What is Join in SQL Server and how many types of Joins in SQLServer?

Inner Join :
Following the statement select the common record from both table base on common column name i.e. ‘salesman_id’ is in both tables.
                select * from Customer inner join Salesman on Customer.salesman_id=Salesman.salesman_id;
        What is Join in SQL Server and how many types of Joins in SQLServer?

Left join :
Following the statement select all data from Left table ‘Customer’ and common data from Right table 

                    select * from Customer left join Salesman on
                    Customer.salesman_id=Salesman.salesman_id;
            What is Join in SQL Server and how many types of Joins in SQLServer?

Right join
The following statement select all record form Right table ‘Salesman’ and the common record from Left table ‘Customer’.
                    select * from Customer right join Salesman on
                    Customer.salesman_id=Salesman.salesman_id;
            What is Join in SQL Server and how many types of Joins in SQLServer?

Full outer join :
 The following SQL statement select all the record from Left table ‘Customer’ and Right table ’Salesman’.
                        select * from Customer full outer join Salesman on
                        Customer.salesman_id=Salesman.salesman_id;

            What is Join in SQL Server and how many types of Joins in SQLServer?