Joins in SQL Server Interview Questions

338
joins in sql server with examples

Keywords | different types of sql joins, outer joins in sql server, inner joins in sql server, self joins in sql server, joins in sql server examples

Understanding Joins in SQL Server

Joins are used to create a single result set of records from multiple tables. Because relational database management systems use normalization and split the data into multiple tables, joins are a required element in queries that need to extract data from these different tables.

So, a join is the mechanism you use to tell SQL Server what field to use from one table to locate and select records from a related table. Remembering the concept of foreign keys, they are a key in one table that equates to a primary key in another table.

Joins in SQL Server typically use a logical operator such as equals or the not equal when comparing the columns, but they can also use other operators such as LIKE, IN, and BETWEEN.

These operators will be covered in the next lesson where we focus on using joins in a query. Joins come in various forms. The default is known as an INNER JOIN. This type of join returns all the matching records in the included tables.

For Example:

Different 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 joins in sql server Joins in SQL Server Interview Questions e1 joins in sql server Joins in SQL Server Interview Questions e1

Job TableJob Table joins in sql server Joins in SQL Server Interview Questions e2 joins in sql server Joins in SQL Server Interview Questions e2

Inner Joins in SQL Server

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

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

Outputinner join output joins in sql server Joins in SQL Server Interview Questions o1

Outer joins in SQL Server

OUTER JOINs are another type of join and they come in three flavors, known as LEFT, RIGHT, and FULL outer joins.

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 joins in sql server Joins in SQL Server Interview Questions Left Join 300x219

Query Example: Get all employee with their jobs?

OutputLeft outer join - output joins in sql server Joins in SQL Server Interview Questions o2

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 joins in sql server Joins in SQL Server Interview Questions o3

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

Outputright outer join sql query joins in sql server Joins in SQL Server Interview Questions o4

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 joins in sql server Joins in SQL Server Interview Questions full join 300x239

Query Example: List Out Employee-Job Relation?

Output
Full Outer Join sql query joins in sql server Joins in SQL Server Interview Questions o5

Self Joins in SQL Server

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.

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

Output
self join sql query joins in sql server Joins in SQL Server Interview Questions o6

Cross Joins in SQL Server (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 joins in sql server Joins in SQL Server Interview Questions cross join rep 300x121

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

Output
Well, you can imagine the output of this cross joins in sql server, 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.

What do you think?

I hope you liked this article on joins in SQL server with examples. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

LEAVE A REPLY

Please enter your comment!
Please enter your name here