SQL JOINS
SQL JOINS are used to combine records from two or more tables in a database.
To apply a join in both tables, it is necessary to have at least one relative(common) column.
We can combine up to 32 tables in a single query.
Syntax:
SELECT table1.column1 , table2.column2 FROM table1.columnname JOIN table2.columnname ;
Let’s understand the JOINS with an example. For Demo, we will be using the Emp and Project Tables.
Table Emp
Table Project
To generate a result set through joins, we can utilize both the emp and project tables.
The relative column between the two tables is Ecode.
Types of Joins in SQL
Here is a list of SQL JOIN types.
INNER JOIN: Returns records that have matching values in both tables.
LEFT OUTER JOIN: Returns all records from the left table, and the matched records from the right table.
RIGHT OUTER JOIN: Returns all records from the right table, and the matched records from the left table.
FULL OUTER JOIN: Returns all records when there is a match in either the left or right table.
INNER JOIN Example :
Objective: In this example, we will retrieve all the records from both the Emp and Project tables where the Ecode column share common values.
SELECT e.Ecode, e.FirstName, e.LastName, p.Project FROM Tbl_Emp as e INNER JOIN Tbl_Project as p on e.Ecode = P.Ecode;
Output:
Note: In this example, the SQL query only retrieved rows where the Ecode was present in both tables.
The SQL query above did not retrieve the rows with ecode 1010 and 1011 because these codes are not present in either of the tables.
LEFT OUTER JOIN Example :
Objective: In this example, we will retrieve all the records from the Emp table(Left Table). If there is a matching Ecode in the Project table(Right Table) and the Emp table, the value will be taken from the Project table else Project table will give NULL value.
SELECT e.Ecode, e.FirstName, e.LastName, p.Project FROM Tbl_Emp as e LEFT JOIN Tbl_Project as p on e.Ecode = P.Ecode;
Output:
Note: In this case, the project table does not have the Ecode for 1010 and 1011, resulting in a null value being displayed for the project.
RIGHT OUTER JOIN Example :
Objective: In this example, we will retrieve all the records from the Emp table(Left Table). If there is a matching Ecode in the Project table(Right Table) and the Emp table, the value will be taken from the Project table else Project table will give NULL value.
SELECT e.Ecode, e.FirstName, e.LastName, p.Project FROM Tbl_Emp as e RIGHT JOIN Tbl_Project as p on e.Ecode = P.Ecode;
Output:
FULL OUTER JOIN Example :
Objective: In this example, we will retrieve all the records when there is a match in the left table(Emp) or right table(Project).
SELECT e.Ecode, e.FirstName, e.LastName, p.Project FROM Tbl_Emp as e FULL JOIN Tbl_Project as p on e.Ecode = P.Ecode;
Output:
Join more than two tables:
Objective: In this example, we will explore how to join more than two tables together.
SELECT e.Ecode, e.FirstName , E.LastName , p.Project , d.Division
FROM Tbl_Emp as e INNER JOIN Tbl_Project as p
on e.Ecode = P.Ecode
INNER JOIN Tbl_Division as d
on p.FunctionID = d.FunctionID;
Output:
