How to use JOINS in SQL Server

Problem: How to get values from two different tables based on common values and logical operators.

Solution:  

JOIN

There are different types of joins in SQL Server.

1. Inner Join INNER JOIN Operation (Microsoft Access SQL)
2. Outer Join LEFT JOIN, RIGHT JOIN Operations (Microsoft Access SQL)
• Left Outer Join
• Right Outer Join
• Full Outer Join 

·         Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, <, > to match the records in two tables. Inner Join includes equi join and natural joins.

·         Outer Join: Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause.

o    Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. returns null column values any row has no matching record in the right side table

o    Right Outer Join: Right Outer Join returns all the rows from right table and returns null values for the rows having no match in the left joined table.

o    Full Outer Join: Full outer join returns all the rows from both the tables joined. If there is any match missing from the left table, then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table.

Example :

I am going to demonstrate an example each for all these above joins.

SELECT * FROM TableA

SELECT * FROM TableB

The following picture shows that table A has three rows and table B has 4 rows.

-- INNER JOIN

SELECT  A.* ,B.Col2 as TableB_Col2

FROM dbo.TableA A

INNER JOIN dbo.TableB B ON B.Col2=A.COl2

The query above shows a INNER JOIN between table A and B and as we can see from the above screenshot both of the tables have 4 in common on the Col2. So the query brings us the match between both these tables.

-- LEFT OUTER JOIN

SELECT A.* ,B.Col2 as TableB_Col2

FROM dbo.TableA A

LEFT OUTER JOIN dbo.TableB B ON B.Col2=A.COl2

The query above has a LEFT OUTER JOIN which brings all the values form table A and returns null values for those we don't have a match from table B. 

-- RIGHT OUTER JOIN

SELECT  A.* ,B.Col2 as TableB_Col2

FROM dbo.TableA A

RIGHT OUTER JOIN dbo.TableB B ON B.Col2=A.COl2

The query above has a RIGHT OUTER JOIN which brings all the values from table B and returns values for the no match of table A.

-- FULL OUTER JOIN

SELECT A.*,B.Col2 as TableB_Col2

FROM dbo.TableA A

FULL OUTER JOIN dbo.TableB B ON B.Col2=A.COl2

This query gives both left and right joins outputs and shows null values for the unmatched on both the tables.