Joins in SQL Server

In this article, I am giving a quick overview about JOINS in SQL Server. After completing this article you will understand:

What is meant by JOIN in SQL Server?

What are different types of JOINS in SQL Server?

Please give your valuable suggestions and feedback to improve this article.

What is meant by JOIN in SQL Server?

Joins in SQL Server are used to retrieve data from multiple tables based on logical relationships between the tables.

A Join condition defines the way two tables are related in a query by.

1-      Specifying the column from each of the table to be used for the join. A typical join condition specifies a foreign key from one table and its associate key in other table.

2-      Specifying a logical operator (i.e =, <>, <) to be used in comparing values from the columns.

What are different types of JOINS in SQL Server?

Types of JOINS in SQL Server.

1-      Inner Join

·         Equi-join

·         Natural Join

2-      Outer Join

·         Left outer Join

·         Right outer join

·         Full outer join

3-      Cross Join

4-      Self Join

To understand the joins we need to populate some dummy data and we will use that data in our examples.

CREATE DATABASE TEACHMESQLSERVER

GO

USE TEACHMESQLSERVERGO

GO

CREATE TABLE DEP(DEPID INT, DEPNAME VARCHAR(MAX))

CREATE TABLE EMP(EMPID INT, EMPNAME VARCHAR(MAX), DEPID INT)

GO

INSERT INTO DEP VALUES(1,'SALES'),(2,'IT'),(3,'HR'),(4,'MARKETING'),(5,'ACCOUNT')

GO

INSERT INTO EMP VALUES(1,'GHANESH',1)

INSERT INTO EMP VALUES(2,'PRASAD',2)

INSERT INTO EMP VALUES(3,'GAUTAM',3)

INSERT INTO EMP VALUES(4,'OJAL',1)

INSERT INTO EMP VALUES(5,'YOGITA',1)

INSERT INTO EMP VALUES(6,'ANVIE',2)

INSERT INTO EMP VALUES(7,'SAURABH',3)

INSERT INTO EMP VALUES(8,'KAPIL',4)

INSERT INTO EMP(EMPID,EMPNAME) VALUES(9,'ATUL')

INSERT INTO EMP(EMPID,EMPNAME) VALUES(10,'SACHIN')

GO

SELECT * FROM DEP

GO

SELECT * FROM EMP

INNER JOIN

Inner join returns the record when at least one match is found in both the tables.  Inner Join can be Equi Join and Non Equi Join.

Equi- Join

In Equi join we only use Equality (=) operator in query for comparing the values from columns. Equi join can be written for Outer Join also.

We can write the query in two ways, first method is the old way of writing INNER join query, second statement is the new style of writing INNER join query.

SELECT DEPNAME,EMPNAME FROM DEP ,EMP WHERE DEP.DEPID=EMP.DEPID

OR

SELECT DEPNAME,EMPNAME FROM DEP INNER JOIN EMP ON DEP.DEPID=EMP.DEPID

As you can see from the result set we are getting only 8 records but we have 10 employees in our EMP table. Our query is returning only those records for which at least one match was found. EMPNAME ATUL and SACHIN were not returned because there was no match found in DEP table for these two records.

Non Equi Join

In Non Equi join we don’t use = operator but we use other available operator (i.e BETWEEN) in query for comparing the values from columns.

Outer Join

We have discussed Inner joins which return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows if there is no match from both the tables. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

SQL Server uses the following ISO keywords for outer joins specified in a FROM clause:

·   LEFT OUTER JOIN or LEFT JOIN- All rows are retrieved from the left table (DEP) referenced with a left outer join plus unmatched information from EMP table.

USE TEACHMESQLSERVER

SELECT DEPNAME,EMPNAME FROM DEP LEFT JOIN EMP ON DEP.DEPID=EMP.DEPID

As you can see from the result set our query is returning all records from LEFT table (DEP), Based on Join condition If match will be found in other table then it will return the value , if no match is found in other table in that case it fill the column value with NULL.

·   RIGHT OUTER JOIN or RIGHT JOIN - All rows are retrieved from the right table (EMP) referenced in a right outer join plus unmatched information from DEP table.

USE TEACHMESQLSERVER

SELECT DEPNAME,EMPNAME FROM DEP RIGHT JOIN EMP ON DEP.DEPID=EMP.DEPID

As you can see from the result set our query is returning all records from RIGHT table (EMP),Based on Join Condition  If match will be found in other table then it will return the value, if no match is found in other table in that case it fill the column value with NULL.

·   FULL OUTER JOIN or FULL JOIN- All rows from both tables are returned in a full outer join.

USE TEACHMESQLSERVER

SELECT DEPNAME,EMPNAME FROM DEP FULL JOIN EMP ON DEP.DEPID=EMP.DEPID

As you can see from the result set our query is returning all records from LEFT table (DEP) and RIGHT table (EMP), Based on Join condition If match will be found in other table then it will return the value , if no match is found in either table in that case it fill the column value with NULL.

Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. If a WHERE Clause is added, the cross join behaves as an Inner Join.

USE TEACHMESQLSERVER

SELECT DEPNAME,EMPNAME FROM DEP, EMP

Total records returned the query is 50 because DEP table has 5 records and EMP table has 10 records.

Self Join

When you join a table with itself then this join is known as Self Join.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.