SQL Server Joins
SQL JOIN
The statement JOIN is used to combine the rows of two or more tables based on a common column.
The different types of JOIN statement used in SQL are
- INNER JOIN or JOIN – combines the data that matches in both tables.
- LEFT JOIN or LEFT OUTER JOIN – combines all the datas of left table with the datas from right table that matches.
- RIGHT JOIN or RIGHT OUTER JOIN – combines all the datas of right table with the datas from left table that matches.
- FULL JOIN or FULL OUTER JOIN – combines all the datas either from left or right table whenever there is a match found.
Let us consider an example:
Create a database with a name “StudentResults”
create database StudentResults
Create a table “studentdetails” with following snippet
CREATE TABLE studentdetails (
regno int,
LastName varchar(255),
FirstName varchar(255),
age int,
dummyno int
);
Let us consider the following datas in table “studentdetails”
Create another table “result” with the snippet
CREATE TABLE result (
dummyno int,
marks int,
result varchar(10)
);
Let us consider the following datas in table “result”
INNER JOIN:
Inner Join is the most simplest Join. When inner join keyword is used in two tables, it will create a resulting table with combining the rows of both the tables until the condition is satisfied
For the above considered example, when inner join statement is used as follows
SELECT studentdetails.regno, studentdetails.FirstName, studentdetails.LastName, studentdetails.age, result.marks, result.result
FROM result
INNER JOIN studentdetails
ON studentdetails.dummyno=result.dummyno;
The resulting table will be
LEFT JOIN:
Left join is also called as Left outer join. When left join keyword is used in two tables, it will create a resulting table with combining all the datas of left table with the datas from right table that matches.
For the considered example, when left join statement is used as follows
SELECT studentdetails.regno, studentdetails.FirstName, studentdetails.LastName, studentdetails.age, result.marks, result.result
FROM studentdetails
LEFT JOIN result
ON studentdetails.dummyno=result.dummyno
ORDER BY studentdetails.regno;
The resulting table will be
RIGHT JOIN:
Right join is also called as right outer join. When right join keyword is used in two tables, it will create a resulting table with combining all the datas of right table with the datas from left table that matches.
For the considered example, when right join statement is used as follows
SELECT studentdetails.regno, studentdetails.FirstName, studentdetails.LastName, studentdetails.age, result.marks, result.result
FROM studentdetails
RIGHT JOIN result
ON studentdetails.dummyno=result.dummyno
ORDER BY studentdetails.regno;
The resulting table will be
FULL JOIN:
Full join is also called as Outer join. When full join keyword is used in two tables, it will create a resulting table with combining all the datas either from left or right table whenever there is a match found.
For the considered example, when full join statement is used as follows
SELECT studentdetails.regno, studentdetails.FirstName, studentdetails.LastName, studentdetails.age, result.marks, result.result
FROM studentdetails
FULL OUTER JOIN result
ON studentdetails.dummyno=result.dummyno
ORDER BY studentdetails.regno;
The resulting table will be
Recent Posts
Categories
- All
- Angularjs training in Chennai
- ASP.NET Core
- dot net training
- dot net training in chennai
- dotnet full stack developer
- Free dotnet training
- information on dotnet
- Learn Java in chennai
- Learn Python at Karaikudi
- learn python online
- learn python online from chennai
- Linq Queries in .net
- mutual funds
- MVC Training Tutorials
- PHP Training in Chennai
- pmp training online
- power apps online training
- Python Training Online
- share market
- Sharepoint framework online training
- SharePoint Freelancers in Chennai
- software testing
- spfx online training
- Stock market
- Uncategorized