What are joins in the SQL Server? What are their different types we are using in the SQL- Server?

We know that in database we can relate the tables to each other by some keys. JOIN is used to create a SQL query from two or more than two tables in the SQL-Server based on the relationship between the two or more tables. If there is a relationship between the tables then it is not a big deal to get the results from the joining tables.

These tables are related to each other by Foreign Key(s) (FK). These foreign keys are the primary key in the referring table. Primary Key (PK) is the column or attribute with a unique value for each row within the table. It is must that Primary Key should be unique to find out the unique record in the table. Primary Key is also helpful to eliminate the duplicity (redundancy) and dependency of the columns.

Let’s have a simple example here to discuss in details:-
I am having the Employee table with E_ID as the Primary Key in the Employee table with FirstName, MiddleName, LastName, Designation columns in it. Then no two records cannot have the same E_ID. It means E_ID as primary key will be distinguish between the two employees

Lets do it one more time, now we are having the Project table. In this table P_ID column is the Primary Key, with ProjectNo and E_ID other columns. E_ID is referring the employees in Employee table without using their details. This is only happening because of the E_ID in the both the tables (Employee and Project)

Here are the types of all the JOINS used in the SQL-Server. JOINs are mainly classified in two kinds i.e. INNER JOIN and OUTER JOINs.

1) INNER JOIN: This join is used to return matching records from the given tables.
2) LEFT OUTER JOIN: – This join is used to return records from the left table in addition with the matching records from the right table. If there is no similar record in the right table, then it will return the NULL values.
3) RIGHT OUTER JOIN: This join is used to return records from the right table in addition with the matching records from left table. If there is no similar record in the left table, then it will return the NULL values.
4) FULL OUTER JOIN: This join is the combination of the both LEFT and RIGHT joins to return records from both the tables when there is a match in both of the tables. Otherwise it will return the NULL values if there is no match at all. When we are using the FULL OUTER JOIN with WHERE clause in the database, then it will give exact opposite result of the INNER JOIN. This will give the records which are not present in the INNER JOIN.
5) CROSS JOIN: This join is the join which does not need any condition to join. The final result from this join will be the mortification of the records from both the tables.
6) SELF JOIN: This join is used in the table itself. We can use it when we want the result set that joins records with other records in the same table. If we are referring the table two time in the same query with the help of the ALIAS for at least one of the instance. We are using this join within the table itself, now table is playing two roles. To make distinguish between these two role we should provide the different ALIAS in the FROM clause.

Thanks,
Sandeep Rajmail
World of Mechatronics