Day-18) joins in sql server with interview questions in hindi|SQL training
| SQL Tutorial for beginners| Intellipaat
Q) What are joins?
A) The SQL Server Joins are used to retrieve the data from two or more related tables. In general, tables are related to each other using the primary key and foreign key relationship but it is not mandatory. The tables involved in the joins must have a common field. And based on that common field the SQL Server JOINS retrieves the records.
Q) What are the different types of joins available in SQL Server?
A) The SQL Server Joins are classified into two types such as
1. ANSI format JOINS
2. NON-ANSI format JOINS
Again the ANSI format joins classified into three types such as
1. Inner join
2. Outer join
3. Cross join
Further, the outer join is divided into three types are as follows
1. Left outer join
2. Right outer join
3. Full outer join
NON-ANSI join in SQL Server are classified into four types such as
1. EQUI join
2. NON-EQUI join
3. SELF-join
4. Natural Join
Examples to Understand Joins in SQL Server:
-- Create Company Database
CREATE DATABASE Company;
-- Create Employee Table
CREATE TABLE Employee (
Id INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Department VARCHAR(100) NOT NULL,
Salary FLOAT NOT NULL,
Gender VARCHAR(45) NOT NULL,
Age INT NOT NULL,
City VARCHAR(45) NOT NULL
);
-- Populate Employee Table
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
-- Create Projects Table
CREATE TABLE Projects (
ProjectId INT PRIMARY KEY IDENTITY(1, 1),
Title VARCHAR(200) NOT NULL,
ClientId INT,
EmployeeId INT,
StartDate DATETIME,
EndDate DATETIME,
FOREIGN KEY (EmployeeId) REFERENCES Employee(Id)
);
-- Populate Projects Table
INSERT INTO Projects (Title, ClientId, EmployeeId, StartDate, EndDate) VALUES
('Develop ecommerse website from scratch', 1, 1003, GETDATE(), (Getdate() + 35)),
('WordPress website for our company', 1, 1002, GETDATE(), (Getdate() + 45)),
('Manage our company servers', 2, 1007, GETDATE(), (Getdate() + 55)),
('Hosting account is not working', 3, 1009, GETDATE(), (Getdate() + 65)),
('MySQL database from my desktop application', 4, 1010, GETDATE(), (Getdate() + 75)),
('Develop new WordPress plugin for my business website', 2, NULL, GETDATE(), (Getdate() + 85)),
('Migrate web application and database to new server', 2, NULL, GETDATE(), (Getdate() + 95)),
('Android Application development', 4, 1004, GETDATE(), (Getdate() + 60)),
('Hosting account is not working', 3, 1001, GETDATE(), (Getdate() + 70)),
('MySQL database from my desktop application', 4, 1008, GETDATE(), (Getdate() + 80)),
('Develop new WordPress plugin for my business website', 2, NULL, GETDATE(), (Getdate() + 90));
Inner Join in SQL Server
The Inner Join in SQL Server is used to return only the matching rows from both the tables involved in the join by removing the non-matching records.
SELECT Id as EmployeeID, Name, Department, City, Title as Project, ClientId
FROM Employee
INNER JOIN Projects
ON Employee.Id = Projects.EmployeeId;