Joins in SQL Server Interview Questions

joins in sql server with examples2

Tags | joins in SQL server with examples, SQL joins in SQL server, inner joins in SQL server, left and right joins in SQL server, multiple joins in SQL server, outer joins in SQL server

Understanding Joins in SQL Server

In this article, I will explain about SQL joins in SQL server. The following article covers all aspects of joins in SQL server with Venn diagrams.

What is meant by joins in SQL Server?

Joins are the commands used to combine data from two or more tables based on the relation between them. The relation between them is specified using columns from each table and relational operators like =,<,> and <>.

Joins in SQL Server Syntax:

Types of Joins in SQL Server

  • Inner Join
  • Outer Join
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join
  • Self Join
  • Cross Join

Before getting into each of them let me introduce table Employee and Job. These two tables will help to discuss each joins in SQL server query.

Employee TableEmployee Table

Job TableJob Table

Inner Join

Inner join displays only rows that match the join condition in both tables.

Instead of INNER JOIN, you can use JOIN also both are same.

Venn - Inner Join

SQL Query Example: Get Jobs of all employees who have at least one job?

Outputinner join output

Left Outer Join

This type of join returns all rows from the left(first) table with second table rows that matches the join condition. rows without a match will have NULL values for second table columns.

Both left outer join and left join are refers exact same operation.

venn-left outer join

SQL Query Example: Get all employee with their jobs?

OutputLeft outer join - output

Right Outer Join

Right join is just an opposite for left join, All rows from right(second) table with matching rows in the first table will display. If no match found NULL values will be returned for first table columns. Both Right Outer Join and Right Join are same.

venn - right outer join

SQL Query Example: Get all Jobs with the employee in charge?

Outputright outer join sql query

Full Outer Join

Full Outer Join returns all rows from both left and right tables. Since it can be treated as a combined result of both LEFT and RIGHT joins.

full join

SQL Query Example: List Out Employee-Job Relation?

Output
Full Outer Join sql query

Self Join

Self Join is a type of join where both left and right tables are the same tables.

In the syntax, you can see TYPE_OF_JOIN it can be any of the following joins like inner join or outer joins or cross join. In case of self-join, you must use alias names for joining tables to differentiate one from another.

SQL Query Example: Query to get details of employee and his supervisor details in one row?

Output
self join sql query

Cross Join (Cartesian Join)

Cross join returns the Cartesian product of the tables hence this type of join also knows as Cartesian Join.

OR

In case of a cross join ON clause is not allowed. instead of ON we may use WHERE for additional filter operation.

cross-join

SQL Query Example: Get all the color combination of available cars ?

Output
Well, you can imagine the output of this cross join, can you guess the number of rows returned by this cross join. Yes, it will 16(4*4), Number of rows returned from cross join operation is the product of the number of rows in both tables.

This is interesting!?

If you change join conditions in ON clause as 1=1 for inner joins and outer join resulting output will be a cross join.

Try it your self!

Did you find this post useful? I hope you liked this article. Please share with me your valuable suggestions and feedback.

LEAVE A REPLY

Please enter your comment!
Please enter your name here