It is used to combine rows from two or more table using related columns between tables. There are various types of Joins in SQL. The following are some of the Joins to discuss in our topic.
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL (OUTER JOIN)
Inner Join
It is one of Join type that is in-placed to filter matching values on common columns on two or more tables. The application of the INNER JOIN is as follow
Syntax: -
SELECT table1.col, table2.col
FROM table1
INNER JOIN table2
ON table1.col=table2.col
Example: -
SELECT Persons.PersonID,Persons.LastName,Persons.FirstName,TestScore.Subject,TestScore.Score
FROM Persons
INNER JOIN TestScore
ON Persons.PersonID=TestScore.PersonID;
Figure 1.1 : – INNER JOIN
Left Join
It is another type of Join that is showing all the left part of a table plus the mashed records from the right on the common value that two or more tables have in their common columns. The application of the Left Join is as follow
Syntax: -
SELECT table1.col, table2.col
FROM table1
LEFT JOIN table2
ON table1.col=table2.col
Example: -
SELECT Persons.PersonID,Persons.LastName,Persons.FirstName,TestScore.Subject,TestScore.Score
FROM Persons
LEFT JOIN TestScore
ON Persons.PersonID=TestScore.PersonID;Figure 1.2 : – LEFT JOIN
Right Join
It is another type of Join that is showing all the Right part of a table plus the matched records from the left side on the common value that two or more tables have in their common columns. The application of the Left Join is as follow
Syntax: -
SELECT table1.col, table2.col
FROM table1
RIGHT JOIN table2
ON table1.col=table2.col
Example: -
SELECT Persons.PersonID,Persons.LastName,Persons.FirstName,TestScore.Subject,TestScore.Score
FROM Persons
RIGHT JOIN TestScore
ON Persons.PersonID=TestScore.PersonID;Figure 1.3 : – RIGHT JOIN
Full Join
It is another type of Join that is showing all records from the Right and left part of a table the matched records f The application of the Left Join is as follow
Syntax: -
SELECT table1.col, table2.col
FROM table1
FULL JOIN table2
ON table1.col=table2.col
Example: -
SELECT Persons.PersonID,Persons.LastName,Persons.FirstName,TestScore.Subject,TestScore.Score
FROM Persons
FULL JOIN TestScore
ON Persons.PersonID=TestScore.PersonID;Figure 1.4 : – FULL JOIN