Blog

Different types of Joins in SQL Server

  • (4.0)
  •   |   140 Ratings

Introduction

Joins are used to retrieve data from multiple table.
In all databases if we are joining “n” table then we are using (n-1) joining condition.

Oracle server having  following types of join:-

1. Equi Join (or) Inner Join
2. Non Equi Join
3. Self Join
4. Outer Join

These 4 joins are also called as 8i joins.

9i Joins (or) Ansi Joins - 

i)  Inner Join.
ii) Left outer Join
iii) Right outer Join
iv) Full outer join
v) Natural Join

Note:- In oracle we can also retrieve data from multiple table without using joins i.e., whenever we are specifying no. of tables within from clause of the select stat then oracle server internally uses cross join but cross join is implemented based on cartision product. That’s why this Joins returns more no. of rows.

Eg:- SQL > select ename, sql, dname, loc from emp, dept;

Enhance your IT skills and proficiency by taking up the SQL Server Training

1.  Equi Join or Inner Join:-

  • Based on equality condition we are retrieving data from multiple tables here joining conditional columns must belongs to same data type.
  • Whenever tables having common column then only we are alloswed to use Equi Joins and also these common columns must belongs to same data type.

Syntax:-

Select col1, col2, ------
From tablename1, tablename2, -------
Where     tablename1 . commoncolname = tablename2 . commoncolname; / Join Condition

Eg:- Select

Ename, sql, deptno, dname, loc
From emp, dept
Where emp.deptno = dept.deptno;
error : column ambiguously defined

Note – For avoiding ambiglity in oracle then we must specify table name along with common column name by using dot(.) operator after select.

Eg – Select

Ename, sql, dept . deptno, dname, loc
From emp, dept
Where emp.deptno = dept . deptno;

Note – Always Equi Joins returns Matching rows only.

[here Deptno 40 doesn’t display when we’re using dept . deptno also]

Generally to avoid future ambiguity then must specify tablename along with every columns within select list by using (.) dot operator.

Using aliasname:-

In Joins we can also create alias names for the table within from clause. These alias names are also called as reference names for the table. These alias name must be different name. Once we are creating alias name in from clause then we must use those aliasnames in place of tablename within select list, within joining condition.

Syntax:-

From tablename1 aliasname1, tablename2 aliasname2;

Eg:- 

Select ename, sql, d.deptno, dname, loc
From emp e, deptd
Where e.deptno = d.deptno;

2. Non-Equl Joins

Based on other than Equality condition (<, < =, >, > = , < >, between, ----)

We are retrieving data from multiple table.

Ex:-

SQL > create tale test, (dept no. number (10));
SQL > insert into test, values (…);
SQL > select * from test1;

DEPT No
10
20

SQL > create table test2 (deptno, number (10));
SQL > insert into test2 values (…);
SQL > select * from test2;

Dept no
10
20
30

SQL > select * from test1;

Dept no
10
20

SQL > select * from test2;

Dept no
10
20
30

SQL > select * from test1, test2
Where test1 . deptno > test2 . deptno;

Dept Dept no
20 10

Note:- In oracle using Non-Equi Join we can also retrieve data from multiple tables when table doesnot have common column values lies between another table to column.

Eg:- 

SQL > select * from emp;
SQL > select * from salgrade;
SQL > select ename, sal, losal, hisal from emp, salgrade
Where sal between losal and hisal;

(OR)

SQL >select ename, sal, losal, hisal from emp, salgrade
Where sal > = losal and sal < = hisal;

Check Out SQL Server Tutorials

3. Self Join

  • Joining a table itself is called self join.
  • Here joining conditional column must belongs to same data type.
  • Generally if we want to compare two column values from different tables and also if we want to retrieve matching rows then we are only using Equi Join where as we want to compare two different column values within a same table then we must use self Join; but here these column must belongs to same data type.
  • Before we are using self Join we must create table alias name in from clause. These alias name must be different names. These alias names are also known as reference name.
  • These alias names internally behaves like a exact table when query execution time.

Syntax:-

From tablename aliasname2 ; tablename aliasname2 ;

4. Outer Join:-

  • This Join is used to retrieve all rows from one table and matching rows from another table.
  • Generally using Equi Join we are retrieving matching rows only if we want to retrieve non matching rows also then we are using JOIN operator (+) within Joining condition of the Equi Join; this is called oracle 8i outer Join.

Note:-

This Join operator can be used only one side at a time within Joining condition.

SQL > select ename, sal, d.deptno, dname, loc
From emp e, deptd
Where e.deptno (+) = d.deptno

e.deptno (+) = Matching rows
All rows = All rows

O/P:-  40 operation BOSTON

Note:-

In oracle if we want to retrieve matching or non-matching rows from all tables then we are using full outer Join. But full outer Join is a 9i Join. Prior to oracle 9i if want to retrieve all data then we are using Join operator within Joining condition one time left side and another time right side.

Ex:-

select
ename , sal, d.deptno, dname, loc
from emp e, dept d
where e.deptno (+) = d.deptno.

Union

Select
ename, sal, d.deptno, dname, loc
From emp e, dept d
Where e.deptno = + deptno;

9i Joins (or) Ansi Joins -

1)  Inner Join.
2) Left outer Join
3) Right outer Join
4) Full outer join
5) Natural Join

1)  Inner Join:-

  • This Join also returns matching rows only, here also Join conditional column must belongs to same datatype. When tables having common columns then only we are allowed to use inner Join.
  • Inner Join performance is very high compare to oracle 8i Equi Join.

Eg:-

Select
Ename, sal, d.deptno, dname, loc
From emp e Join dept d
on e.deptno = d . deptno;

2) Left outer Join:-

This Join always return all rows from left side table and matching rows from right side table and also returns null values in place of non-matching rows in another table. 

Eg:- SQL > select * from z1 left outer Join z2 on z1 . a = z2 . a and z1 . b = z2 . b;

A B C A B
x y z x y
p q r - -

3) Right outer Join:-

This Join always return all rows from Right side table and Matching rows from left side table and also returns null values in place of non-matching rows in another table.

Eg:- SQL > select * from z1 right outer Join z2 on z1 . a = z2 . a and z1 . b = z2 . b;

A B C A B
x y z x y
- - - s t

4) Full outer join:-

  • This Join returns all rows from all tables because it is combination of left, right outer Join.
  • This Join also returns null values in place of not matching rows in other table.

Eg:- SQL > select * from Z1 full outer Join z2 on z1 . a = z2 . a and z1 . b = 2 . b;

A B C A B
x y z x y
p q r - -
- - - s t

5) Natural Join

  • This Join also returns matching rows only. This Join performance very high compare to inner Join.
  • In this Join we are not required to use Joining condition explicitly. But in this case resource tables must have a common column name based on this common column oracle server only internally automatically establishes Joining condition.

Frequently Asked SQL Server Interview Questions

Syntax-

Select * from tablename1 natural Join tablename2;

Note:-

Whenever we are using Natural Join oracle server always returns common columns one time only because natural Join internally use using clause.

Eg:-  Select * from z1 natural Join z2;

O/P:- 

A B C
x y z

Note: When ever we using natural Join also then we aren’t allow to use alias name to Joining conditional column because natural Join internally uses Using Clause.

Eg:- SQL > select ename, sal, deptno, dname, loc From emp e natural Join dept d;

Cross Join

Select ename, sal, dname, loc from emp cross Join dept;

When;

emp – 14 rows
Dept – 14 rows

Ex:- Join 3 tables

8i Joins 9i Joins (or) ANSI Join
Syntax:- Syntax:-
Select col1, col2, …. From table1, table2, table3 Select col1, col2, ….
Where table1 . common col = table2 . commoncol and

From table1 Join table2

on table1 . commoncol = table2 . commoncol

Table2 . commoncol = table3 . commoncol

Join table3

On table2 . commoncol = table3 . commoncol            

 

List of Related Microsoft Certification Courses:

 SSRS  Power BI
 SSAS  SQL Server
 SCCM  SQL Server DBA
 SharePoint  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator

 


Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules