What are the different types of joins available in sql server?
There are 3 different types of joins available in sql server, and they are
1. Cross Join
2. Inner Join or Join
3. Outer Join
Outer Join is again divided into 3 types as shown below.
1. Left Outer Join or Left Join
2. Right Outer Join or Right Join
3. Full Outer Join or Full Join
You might have heard about self join, but self join is not a different type of join. A self join means joining a table with itself. We can have an inner self join or outer self join. Read this sql server interview question, to understand self join in a greater detail.
What is cross join. Explain with an example?
Let us understand Cross Join with an example. Create 2 tables Company and Candidate. Use the script below to create these tables and populate them. CompanyId column in Candidate Table is a foreign key referencing CompanyId in Company Table.
CREATE TABLE Company
(
CompanyId TinyInt Identity Primary Key,
CompanyName Nvarchar(50) NULL
)
GO
INSERT Company VALUES('DELL')
INSERT Company VALUES('HP')
INSERT Company VALUES('IBM')
INSERT Company VALUES('Microsoft')
GO
CREATE TABLE Candidate
(
CandidateId tinyint identity primary key,
FullName nvarchar(50) NULL,
CompanyId tinyint REFERENCES Company(CompanyId)
)
GO
INSERT Candidate VALUES('Ron',1)
INSERT Candidate VALUES('Pete',2)
INSERT Candidate VALUES('Steve',3)
INSERT Candidate VALUES('Steve',NULL)
INSERT Candidate VALUES('Ravi',1)
INSERT Candidate VALUES('Raj',3)
INSERT Candidate VALUES('Kiran',NULL)
GO
A cross join 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. A query involving a CROSS JOIN for the Candidate and Company Table is shown below.
SELECT Cand.CandidateId,Cand.FullName,Cand.CompanyId, Comp.CompanyId,Comp.CompanyName
FROM Candidate Cand
CROSS JOIN Company Comp
If we run the above query, we produce the result set shown in the image below.
Key Points to remember about CROSS JOIN.
1. A cross join produces the Cartesian product of the tables involved in the join.This mean every row in the Left Table is joined to every row in the Right Table. Candidate is LEFT Table and Company is RIGHT Table. In our example we have 28 total number of rows in the result set. 7 rows in the Candidate table multiplied by 4 rows in the Company Table.
2. In real time scenarios we rarley use CROSS JOIN. Most often we use either INNER JOIN or LEFT OUTER JOIN.
3. CROSS JOIN does not have an ON clause with a Join Condition. All the other JOINS use ON clause with a Join Condition.
4. Using an ON clause on a CROSS JOIN would generate a syntax error.
Note: Understanding the above key points will help you answer any follow up interview questions on cross join in sql server.
If you can think of any other sql server interview questions please post them as comments, so they will be useful to other users like you. This will be a great help from your side to improve this site.
what are the global variables in sql server?what is the use of it?
ReplyDeleteHello Venket, I would like to admire and thank you for what you are doing ! You have been a vessel of God helping those who can not get education for free and giving your time, life for us and it is really a tremendous virtue. May God bless you .
ReplyDeleteI have a question though :
could you explain what dynamic sql is with example as usual ?
join batch
DeleteHi Venkat, thanks for sharing this wonderful question batches. It really helped me to take up an interview yday. i am not sure whether i am into the job or not but i felt happy tht i understood some basics which i have never paid attention before. Thanks to you.
ReplyDeleteI would like to add one more question of the difference between UNION and UNION ALL. Please post this difference too.
Thanks
Jay.
Union will remove the duplicates and display a Sorted Table where as in Union All displays duplicate data in an Unsorted output. Bcoz of the Sorting Union is slower in terms of execution when compared to Union All.
Deletehow to retrieve data from more then 2 tables.
ReplyDeletewith the help of joins
Deletehi venkat can u tell me how to delete duplicate rows in a table?
ReplyDeleteHey you can delete duplicate records with the help of CTE in sql, Here the example -:
DeleteWITH cte AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY emp_name ORDER BY emp_name)'RowRank'
FROM Employee_Test
)
DELETE FROM cte WHERE RowRank > 1
hmm.. u can just create a view using select distinct.. if you are not really having problems with your disk space.. just a suggestion..
DeleteCTE is optional:
DeleteDELETE FROM Employees
WHERE Id IN
(
SELECT e.Id
FROM
(
SELECT
*,
ROW_NUMBER() OVER
(
PARTITION BY
e.Name, e.Dept, e.Gender, e.Country, e.Salary, e.ManagerId
ORDER BY
e.Id
) AS RowRank
FROM Employees e
) e
WHERE e.RowRank > 1
);
I was asked in one of the interview, employee having 10 employees with salaries. Find 10th maximum salary from table. Please let me know what would be the query
ReplyDeleteWITH CTE AS
ReplyDelete(
SELECT name,sallery,DENSE_RANK() OVER( ORDER BY sallery DESC) ranks
FROM tblemployee
)
SELECT DISTINCT sallery
FROM CTE
WHERE ranks = 10
OR
SELECT TOP(1) sallery
FROM(
SELECT DISTINCT TOP(10) sallery
FROM tblemployee
ORDER BY Sallery DESC
) as x
ORDER BY Sallery