SQL Tutorial – MSSQL Server – SQL Joins

2
  Uncategorized

SQL Tutorial – MSSQL Server – SQL Joins combines records from two or more tables in database based on common columns. SQL joins has various types and we will look at each of them one by one.

For looking at examples we will have following tables

LastNameTable
Id LastName
1 Patil
3 Pedro
6 Obama
7 Romny
FirstNameTable
Id FirstName
1 Shridhar
2 Swapnil
3 Sam
4 John

1. JOIN or INNER JOIN :

  • Most common type used in applications
  • Return new result combining columns matching to predicate

e.g. SQL query

select f.*, l.* from Sample.dbo.FirstNameTable as f
inner join sample.dbo.LastNameTable as l
on f.Id = l.Id

select f.*, l.* from Sample.dbo.FirstNameTable as f
join sample.dbo.LastNameTable as l
on f.Id = l.Id

Result

Id FirstName Id LastName
1 Shridhar 1 Patil
3 Sam 3 Pedro

2. Outer Joins do not need each record in A table to match with B table.  These are divided further into 3 types

a. Left Outer Join returns results with all matching records in both tables plus all non matching records of left table. If records are not matching with right table then null is returned.

SQL query:

select f.*,l.* from Sample.dbo.FirstNameTable as f
left outer join sample.dbo.LastNameTable as l
on f.Id = l.Id

Result

Id FirstName Id LastName
1 Shridhar 1 Patil
2 Swapnil null null
3 Sam 3 Pedro
4 John null null

b. Right Outer Join returns results with all matching records in both tables plus all non matching records of right table. If records are not matching with left table then null is returned.

SQL query:

select f.*,l.* from Sample.dbo.FirstNameTable as f
right outer join sample.dbo.LastNameTable as l
on f.Id = l.Id

Result

Id FirstName Id LastName
1 Shridhar 1 Patil
3 Sam 3 Pedro
null null 6 Obama
null null 7 Romny

C. Full Outer Join returns results with all matching records in both tables plus all non matching records of both table. If records are not matching with right table then null is returned.

SQL query:

select f.*,l.* from Sample.dbo.FirstNameTable as f
full outer join sample.dbo.LastNameTable as l
on f.Id = l.Id

Result

Id FirstName Id LastName
1 Shridhar 1 Patil
2 Swapnil null null
3 Sam 3 Pedro
4 John null null
 null null 6 Obama
null null 7 Romny

C. Cross Join returns Cartesian product of both tables. This join do not need any condition to match for.

SQL query:

select f.*,l.* from Sample.dbo.FirstNameTable as f
cross join sample.dbo.LastNameTable as l

Result

Id FirstName Id LastName
1 Shridhar 1 Patil
2 Swapnil 1 Patil
3 Sam 1 Patil
4 John 1 Patil
1 Shridhar 3 Pedro
2 Swapnil 3 Pedro
3 Sam 3 Pedro
4 John 3 Pedro
1 Shridhar 6 Obama
2 Swapnil 6 Obama
3 Sam 6 Obama
4 John 6 Obama
1 Shridhar 7 Romny
2 Swapnil 7 Romny
3 Sam 7 Romny
4 John 7 Romny

To get record just out of left or right tables excluding common record you can add where f.Id IS null or where l.Id IS null. If you want records out of both tables excluding common records put both conditions in or.

Sql-tutorial-SQL_JOINS

Sql-tutorial-SQL_JOINS

2 thoughts on “SQL Tutorial – MSSQL Server – SQL Joins

Leave a Reply

Your email address will not be published. Required fields are marked *