Join query in detail for SQL Server

We have three types of Joins in sql.

1. Inner join
2. Outer join
3. Cross join

1. Inner join:
Inner join is the default type of join, it will produces the result set, which contains matched rows only.

We have three types of Joins in INNER JOIN

a. Self Join
b. Equi Join
c. Natural join

a. Self Join:
A join joins with itself is called self join, working with self joins we use alias tables.

b. Equi Join:
DISPLAYS ALL THE MATHCING ROWS FROM JOINED TABLE. AND ALSO DISPLAYS REDUNDANT VALUES. IN THIS WE USE * SIGN TO JOIN THE TABLE.

Example.
Create table EMPNAME (ID int, EMPNAME varchar(20))
CREATE table EMPADDRESS (ID int, ADDRESS varchar(20))

INSERT INTO EMPNAME VALUES(1, 'DEEPAN')
INSERT INTO EMPNAME VALUES(2, 'RAMESH')
INSERT INTO EMPNAME VALUES(3, 'PRADEEP')
INSERT INTO EMPADDRESS VALUES(1, 'BANGALORE')
INSERT INTO EMPADDRESS VALUES(2, 'DELHI')
INSERT INTO EMPADDRESS VALUES(4, 'DELHI')

SELECT * FROM EMPNAME
ID EMPNAME
----------- --------------------
1 DEEPAN
2 RAMESH
3 PRADEEP

SELECT * FROM EMPADDRESS
ID ADDRESS
----------- --------------------
1 BANGALORE
2 DELHI
4 DELHI

Equi Join example
SELECT * FROM EMPNAME
JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

c. Natural Join
DISPLAYS ALL THE MATHCING ROWS FROM
JOINED TABLE.IT RESTRICT
REDUNDANT VALUES.

Example:
SELECT A.*, E.ID, E.EMPNAME FROM EMPADDRESS A
INNER JOIN
EMPNAME E ON E.ID = A.ID

This natural join query will return all the columns of categories table and prodcutId and productName from products table. We can further modify this natural inner join query as per your requirements to visualize the data by specifying the column names of categories table also.

Inner Join Query Example by specifying column names:
SELECT A.ID, A.ADDRESS, E.ID, E.EMPNAME FROM EMPADDRESS A INNER JOIN
EMPNAME E ON E.ID = A.ID

This inner join query will display only the specified column names of both the tables.

Example : INNER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

OR

SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
INNER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID


Output of the above JOIN Query (i.e Inner Query)
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI

2. Outer join:
Outer join produces the results, which contains matched rows and unmatched rows.

Outer join is further classified as three types.

a. Left outer join
b. Right outer join
c. Full outer join.

a. Left outer join:
Left outer join produces the results, which contains all the rows from left table and matched rows from right table.

Example : LEFT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
LEFT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above LEFT OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
3 PRADEEP NULL

b.Right outer join:
Right outer join produces the resultset, which contains all the rows from right table and matched rows from left table.

Example : RIGHT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
RIGHT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above RIGHT OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
NULL NULL DELHI

c. Full outer join:
Full outer join produces the resultset, which contains all the rows from left table and all the rows from right table.

Example : FULL OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
FULL OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above FULL OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
3 PRADEEP NULL
NULL NULL DELHI

3. Cross join:
A join without having any condition is known as cross join, in cross join every row in first table is joins with every row in second table. Cross join is nothing but cartesian product.

Example : CROSS JOIN
===================================================================
SELECT * FROM EMPNAME CROSS JOIN EMPADDRESS