Joins in SQL Server 2014 PART I || INNER JOIN and EQUI JOIN in SQL Server 2014
This video will show to what is joins in SQL server and how to use them with an example. Please try to follow next few sessions to cover all joins in SQL Server 2014.
Next session on SQL Server Joins
PART II
• Joins in SQL Server 2014 PART II || LEFT, ...
PART III
• Joins in SQL Server 2014 PART III || SELF ...
**********************************************************************
What is join??
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field
between them.
There are many types of join.
• Inner Join
1. Equi-join
2. Natural Join
• Outer Join
1. Left outer Join
2. Right outer join
3. Full outer join
• Cross Join
• Self Join
Join is very useful to fetching records from multiple tables with reference to common column
between them.
To understand join with example, we have to create two tables in SQL Server database.
1. Employee
CREATE TABLE EMPLOYEE(
ID INT,
USERNAME VARCHAR(50),
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50),
DEPARTID INT
)
2. Departments
CREATE TABLE DEPARTMENTS(
ID INT ,
DEPARTMENTNAME VARCHAR(50)
)
****************************************************************************
INSERT INTO EMPLOYEE
SELECT 1,'RAM GOPAL','RAM','GOPAL',1
UNION
SELECT 2,'VARUN VARMA','VARUN','VARMA',1
UNION
SELECT 3,'ARUN KUMAR','ARUN','KUMAR',2
UNION
SELECT 4,'RANJIT SINGH','RANJIT','SINGH',3
UNION
SELECT 5,'AMIT DAS','AMIT','DAS',3
UNION
SELECT 6,'SUDHEER DESAI','SUDHEER','DESAI',3
UNION
SELECT 7,'ANWESH SHARMA','ANWESH','SHARMA',1
UNION
SELECT 8,'AMIT SINGH','AMIT','SINGH',NULL
INSERT INTO DEPARTMENTS
SELECT 1,'IT'
UNION
SELECT 2,'EC'
UNION
SELECT 3,'MECHANICAL'
UNION
SELECT 4,'CIVIL'
UNION
SELECT 5,'BIOMECHANICAL'
UNION
SELECT 6,'CHEMICAL'
Inner Join
The join that displays only the rows that have a match in both the joined tables is known as inner join.
SELECT E1.USERNAME,E1.FIRSTNAME,E1.LASTNAME,E2.DEPARTMENTNAME DEPT FROM EMPLOYEE E1 INNER JOIN DEPARTMENTS E2 ON E1.DEPARTID=E2.ID
It gives matched rows from both tables with reference to DepartID of first table and id of second table like this.
****************************************************************************
Equi-Join
Equi join is a special type of join in which we use only equality operator. Hence, when you make a query forjoin using equality operator, then that join query comes under Equi join.
Equi join has only (=) operator in join condition.
Equi join can be inner join, left outer join, right outer join.
Check the query for equi-join:
SELECT * FROM EMPLOYEE E1 JOIN DEPARTMENTS E2 ON E1.DEPARTID = E2.ID