Joins in MS SQL Server
A database comprises tables and each table in case of RDBMS is called a relation.
Let us consider a sample database named University and it has two tables named Student and Marks. If a user wants to transfer a certain set of rows, insert into select statement is used along with the query. But if a user wants a whole set of rows from both tables or if a user wants to extract only a selected set of rows from both tables, joins are a perfect match to use in query.
Joins are clauses that extracts data from two tables to make a meaningful set of new data. Joins are used for a maximum of two tables only. The data that is extracted from tables forms a new table or relation that is different from previous tables that is being used for data extraction. Microsoft version of SQL supports different types of joins like – left join, right join, self join, inner join, full outer join, and cross join.
Example –
The two tables namely Student and Marks are of the same database University. If a user wants to join all rows from both tables, query must be given as –
select * from student full join marks
Table – Student
Name | Rollno | Course |
---|---|---|
Aisha | 111 | CSE |
Mina | 112 | EEE |
Shilpa | 113 | Mech |
Table – Marks
Name | Age | GPA |
---|---|---|
Aisha | 18 | 9.5 |
Mina | 19 | 8.9 |
Shilpa | 18 | 7.7 |
We can see that tables have been joined without missing out on a single column. To extract only a few rows, we can use other joins according to convenience.
Name | Rollno | Course | Name | Age | GPA |
---|---|---|---|---|---|
Aisha | 111 | CSE | Aisha | 18 | 9.5 |
Mina | 112 | EEE | Mina | 19 | 8.9 |
Shilpa | 113 | Mech | Shilpa | 18 | 7.7 |