Joins In SQL Server

In this article, we will learn about Joins in SQL Server. I will explain all SQL Joins with an example.

Introduction

Joins are used to fetch data from two or more two tables based on some conditions. Joins are one of the most important concepts of SQL Server. We can retrieve data from multiple tables using Joins.

Classification Of Joins in SQL

SQL

When we retrieve the data from multiple tables with one keyword condition then this is called ANSI format join.

When we retrieve data from multiple tables based on where keyword condition then it is called NON-ANSI format Joins.

Note. Inner Join is the default Join in Joins.

Syntax of Joins

SELECT /* <List of columns> */
FROM Tablename1
    <Join> Tablename2
    ON <Join Condition>
WHERE <Where Condition>;

Let us understand Joins with an example.

Create Two Tables in SQL

CREATE TABLE customer (
    Id INT,
    Name VARCHAR(50),
    Mobile INT,
    CustomerId INT
);

CREATE TABLE company (
    CompanyId INT,
    CompanyName VARCHAR(50),
    Salary DECIMAL(6,2)
);

Insert some data into Tables in SQL

-- Insert into customer table
INSERT INTO customer VALUES (1, 'A', 89898989, 101);
INSERT INTO customer VALUES (2, 'B', 78787877, 102);
INSERT INTO customer VALUES (3, 'C', 98323232, 103);

-- Insert into company table
INSERT INTO company VALUES (101, 'C1 Tech', 5000);
INSERT INTO company VALUES (102, 'Cone Tech', 1000);
INSERT INTO company VALUES (104, 'Netvision Tech', 3500);
INSERT INTO company VALUES (105, 'S Tech', 1500);

Equi Join in SQL

When we retrieve the data from multiple tables based on an equality condition then this is called an Equi Join.

This Join process supports only one operator equal Operator (=)

When we use Equi Join we should maintain a common column name and that column should contain the same data type.

Example

SELECT c.id, c.name, c.mobile, c.CustomerId, com.Companyid, com.CompanyName, com.Salary
FROM customer c
INNER JOIN company com ON c.CustomerId = com.Companyid;

Result

Result

Alias Name

Alias Name is a duplicate or Alternative Name.

We can define alias names in two levels,

Column-level alias name
Table-level alias name

When we create a duplicate name for a column then it is called column level alias name.

Syntax

<Column Name> AS <column alias Name>

When we create the alternative name for the tables in the database then it is called table level alias name.

Syntax

<TableName> AS <TableAliasName>

Note. Alias names are mostly implemented in Joins.

Inner Join in SQL

Inner Join is used for retrieving data from multiple Tables. When we use inner Join we should use a common column name and the datatype is also the same in the Table.

Example

select * from customer c inner join company com on c.CustomerId=com.Companyid

Result

Multiple tables

Outer Join in SQL

Outer Join is an extension of the Inner Join.

In Inner Join mechanisms, the user will get matching data from the Tables and leave unmatching data from the Tables.

To overcome the drawback we use the Outer Join. By using outer we can retrieve matching data and also unmatch data from the tables at the same time.

Type of Outer Join in SQL

Outer Join

Left Outer Join in SQL

It retrieves matching data from multiple tables and also unmatches data from the left-hand side table only.

Right Outer Join in SQL

It retrieves matching data from multiple tables and also unmatches data from the right-hand side table only.

Example of Left Outer Join

Write a query to display matching data from the customer, and company and unmatch data from the customer table.

select * from customer c left outer join company com on c.CustomerId=com.Companyid

Result

Customer table

Example of Right Outer Join

Write a query to display matching data from the customer and company and unmatch data from the company table.

select * from customer c right outer join company com on c.CustomerId=com.Companyid

Right Outer Join

Cross Join in SQL

When we Join the two table's information without any condition it is known as Cross Join

In the cross-join mechanism, each record of a first Table is joined with each record of the second Table.

For example, if the first Table contains A number of records and the second Table contains B number of records then we will get the cross product A*B records.

Example

select * from customer cross join company

Table contains number

Non-Equi Join in SQL

When we retrieve the data from multiple tables based on any condition except an Equality condition, it is known as a Non-Equi Join.

When we implement Non-Equi Join there is no requirement to maintain a common column in the Table.

It supports all operators. To understand this let us create two tables and insert values.

CREATE TABLE employee (
    id INT,
    name VARCHAR(50),
    salary INT,
    Mid INT
);

CREATE TABLE salary_Range (
    sno INT,
    Lowsal INT,
    Highsal INT
);

INSERT INTO employee VALUES (1, 'A', 2500, 101);
INSERT INTO employee VALUES (2, 'B', 3200, 108);
INSERT INTO employee VALUES (3, 'C', 400, 103);
INSERT INTO employee VALUES (4, 'D', 8200, 109);
INSERT INTO employee VALUES (5, 'E', 1100, 101);

INSERT INTO salary_Range VALUES (1, 1500, 2800);
INSERT INTO salary_Range VALUES (2, 3500, 5200);
INSERT INTO salary_Range VALUES (3, 6500, 8500);

Example. Write a Query to retrieve employee details whose salary is greater than the lowest and less than the highest salary.

select * from employee,salary_Range where (salary>Lowsal)and (salary<Highsal)

Result

Query to retrieve employee

Self Join SQL

A Table joining by itself is known as a Self Join.

Self Join can be implemented when any 2 columns have some relationship within the same Table.

It can be worked on a single Table only.

When we use Self Join on a Table then we should create alias names for the Table.

Without alias names, we cannot implement Self Join.

A Table contains any number of alias names.

Natural Join in SQL

Natural Join is used to avoid duplicate columns from a Resultset.

Example

SELECT id, name, mobile, customerid, companyname, salary
FROM customer c, company com
WHERE c.customerid = com.companyid;

Result

Join in SQL

Syntax to Join more than two tables

SELECT <Column name>
FROM <Table1>
<Join Key> <Table2> ON <Condition>
<Join Key> <Table3> ON <Condition>

Create a Table

CREATE TABLE Reg (
    Regno int,
    Regdate datetime,
    cid int
)

INSERT INTO Reg VALUES (1, '2017-03-01', 101)
INSERT INTO Reg VALUES (1, '2017-04-12', 102)
INSERT INTO Reg VALUES (1, '2017-06-09', 108)

Example

select * from customer c ,company com,Reg r where c.customerid=com.companyid and com.companyid=r.cid

Result

Table

Summary

In this article, I have explained Join in SQL with examples. This article is very helpful for beginners.