Basic SQL Server Interview Questions on Joins



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.

13 comments:

  1. what are the global variables in sql server?what is the use of it?

    ReplyDelete
  2. Hello 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 .
    I have a question though :

    could you explain what dynamic sql is with example as usual ?

    ReplyDelete
  3. Hi 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.

    I would like to add one more question of the difference between UNION and UNION ALL. Please post this difference too.

    Thanks
    Jay.

    ReplyDelete
    Replies
    1. 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.

      Delete
  4. how to retrieve data from more then 2 tables.

    ReplyDelete
  5. hi venkat can u tell me how to delete duplicate rows in a table?

    ReplyDelete
    Replies
    1. Hey you can delete duplicate records with the help of CTE in sql, Here the example -:


      WITH cte AS
      (
      SELECT *,ROW_NUMBER() OVER(PARTITION BY emp_name ORDER BY emp_name)'RowRank'
      FROM Employee_Test
      )
      DELETE FROM cte WHERE RowRank > 1

      Delete
    2. hmm.. u can just create a view using select distinct.. if you are not really having problems with your disk space.. just a suggestion..

      Delete
    3. CTE is optional:

      DELETE 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
      );

      Delete
  6. 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

    ReplyDelete
  7. WITH CTE AS
    (
    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

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us