Joins in SQL Server

In this blog, I’m trying to explain the concept of joins in SQL server and its types.

Databases are usually comprised of several tables that are related to one another in some way. You need to pull the information from more than one table at a time so that you get the desired result.

Joins are used to extract data from more than one table at a time and produce the information as a single result set. Join is used to link or connect tables on a common column and return the record that match in those columns.

In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table.

Before enter into Joins concept first design two tables in database and enter data like as shown below

 

Give the table name as CustomerTable. Here CUSTID is the primary key.

 

Give the table name as ProductTable. Here PRODUCTID is the primary key and CUSTID is the foreign key.

 

 Types of Joins

·         Inner Join

·         Outer Join

·         Self Join

 

Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join.

This is default join in the query and view designer.

 

Syntax:

SELECT T1.ColumnName, T2.ColumnName

FROM TableName1 T1

INNER JOIN TableName2 T2

ON T1.ColumnName=T2.ColumnName

 

Example

SELECT C.CUSTNAME,C.ADDRESS,P.PRODUCTNAME,P.PRICE

FROM CustomerTable C

INNER JOIN ProductTable P

ON C.CUSTID=P.CUSTID

Output

 

Types of Inner Join

·         Equi Join

·         Natural Join

·         Cross Join

Equi Join

In an equi join, column values are compared for equality and even the duplicate columns are displayed.

Syntax:

SELECT * FROM TableName1 T1

Join TableName2 T2

ON T1.ColumnName=T2.ColumnName

 

Example

SELECT * FROM CustomerTable C

Join ProductTable P

ON C.CUSTID=P.CUSTID

Output

 

Natural Join

In natural join, the duplicate columns are not there. Thus, when you join the two tables, you can select all the fields from one table and specify the fields that you want from the other table.

Syntax:

SELECT * FROM TableName1

NATURAL JOIN TableName2

Example

SELECT * FROM CustomerTable

NATURAL Join ProductTable

Natural Joins won’t work in SQL Server(only supports in Oracle) it will throw a syntax error.

 

Cross Join

A cross join produces the Cartesian product of the tables those involved in the join. The size of the Cartesian product is the number of the rows in the first table multiplied by the number of the rows in the second table.

Syntax:

SELECT * FROM TableName1

CROSS JOIN TableName2

 

Example

SELECT * FROM CustomerTable

CROSS JOIN ProductTable

Output

 

 

Outer Join

Outer joins restrict rows from one table while allowing all rows from the second table as a result set.

There are three types of outer joins:

·         Left Outer Join

·         Right Outer Join

·         Full Outer Join

Left Outer Join

It includes all the rows from the first table and only the matching rows from the second table.

Syntax:

SELECT ColumnName FROM TableName1 T1

LEFT OUTER JOIN TableName2 T2

ON T1.ColumnName=T2.ColumnName

 

Example

SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME FROM CustomerTable C

LEFT OUTER JOIN ProductTable P

ON C.CUSTID=P.CUSTID

Output

 

 

Right Outer Join

It includes all the rows from the second table and only the matching rows from the table.

Syntax:

SELECT ColumnName FROM TableName1 T1

RIGHT OUTER JOIN TableName2 T2

ON T1.ColumnName=T2.ColumnName

 

Example

SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME,P.PRICE FROM CustomerTable C

RIGHT OUTER JOIN ProductTable P

ON C.CUSTID=P.CUSTID

 

Output

 

 

Full Outer Join

It includes all the rows, matching as well as non-matching.

 

Syntax:

SELECT ColumnName FROM TableName1 T1

FULL OUTER JOIN TableName2 T2

ON T1.ColumnName=T2.ColumnName

 

Example

SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME,P.PRICE

FROM CustomerTable C

FULL OUTER JOIN ProductTable P

ON C.CUSTID=P.CUSTID

 

Output

 

 

Self Join

Self join correlates rows of a table with the other rows of the same table. It is used when a table has to be joined to itself to produce results.

 

Example

First you have to create a table EmpTable and enter the details like this:

SELECT E2.EMPNAME,E1.EMPNAME as 'MANAGER'

FROM EmpTable E1

INNER JOIN EmpTable E2

ON E1.EMPID=E2.EMPMGRID

Output