Joins in SQL SERVER

Sql Server is all about data & the data may be stored on the multiple places(tables) which could be queried to trace information at any point of time. Whenever thinking about retrieving the concerned information from all the tables in the form of report the joins come in picture. Joins are an very important part of database & without using them it is really impossible to even think about database.

These are the most used & main type of joins in SqlServer,

1. Inner Join
2. Outer Join
3. Cross Join

Use below mentioned script on any of your test database for understanding the various types with example.

CREATE TABLE STUDENTS(STUDENTID INT IDENTITY(1,1), NAME VARCHAR(100), MOBILENO BIGINT)

CREATE TABLE SPECIALCOURSE(ID INT IDENTITY(1,1), STUDENTID INT, STATUS BIT)

INSERT INTO STUDENTS(NAME, MOBILENO)

SELECT 'AJAY',9797979797

UNION ALL

SELECT 'SANJAY',1234567890

UNION ALL

SELECT 'SAMMY',9874563210

UNION ALL

SELECT 'SAMUAL',8789456512

UNION ALL

SELECT 'GANESH',7412589630

UNION ALL

SELECT 'MAHESH', 9632587400

INSERT INTO SPECIALCOURSE(STUDENTID, STATUS)

SELECT 1,0

UNION ALL

SELECT 3,1

Inner Join

1.1) Inner Join:

Inner join is the most used join type in SqlServer. Whenever trying to check the concerned data only from the system the inner join is used. Inner join is very useful whenever user needs to get the only matching data from the concerned tables. To use this join type the matching columns from all the required tables needs to be mention so while executing query SqlServer engine consider the specified column for returning the proper information.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A INNER JOIN SPECIALCOURSE B ON A.STUDENTID = B.STUDENTID

1.2)Self Join:

Another type of inner join is Self join which could be defined as join within same table for extraction of related data within same table. The best example for this will be Manager/Employee relationship between same table. Unfortunately I have not designed my table which has this relationship otherwise I could have showed the example query.

Outer Join

2.1) Left Outer Join:

Whenever we wish to get all the data from the left table we need to use Left Outer Join.. Left join will return all the matching rows from the left table & in case of no match it will return NULL.
It is really useful when we want to find all the information from one side table with the relevant information from second table.

Here the left table is STUDENTS & in second table i.e. SPECIALCOURSE there is some information about few students who have joined special course.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A LEFT JOIN SPECIALCOURSE B ON A.STUDENTID = B.STUDENTID

2.2) Right Outer Join:

There may be a situation during the query when user needs information from second second & the matching or non-matching records from first table. In these scenarios developer will not swap the table from left to right as there is already a provision called Right Outer Join where the purpose can be achieved by using Right Outer Join.

In this scenario user will receive all the the information from second(Right) table & the matching information from first table.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A RIGHT JOIN SPECIALCOURSE B ON A.STUDENTID = B.STUDENTID

2.3) FULL Outer Join:

FULL outer join do not look for the match between the two tables & returns all the information from both the tables. This kind of results may be required when there is a need of all the data from system which both the tables.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A FULL OUTER JOIN SPECIALCOURSE B ON A.STUDENTID = B.STUDENTID

CROSS JOIN

Working with Cross join like working with multiplication between tables. Whenever there is some kind of situation when a user needs to map all the records from left table with all records of left table then in this situation the Cross join is brought into picture.

Here in this below query Students table 6 records 6 records & SPECIALCOURSE table has 2 records. As mentioned above it will return the multiplication the output will be 6 * 2 = 12 records & in output all the students will be mapped to the all SPECIALCOURSE records.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A CROSS JOIN SPECIALCOURSE B

Note: If the any of the two tables do not have record then there will be no output from this join.

Like other join type this join type needs not to have a ON condition.