A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Different Types of SQL JOINs
- Inner Join:
- Returns records that have matching values in both tables
- Joinand Inner Join will return the same result.
- Inneris the default join type for Join, so when you write Join the parser actually writes Inner Join.
Syntax:
-
- Select column_name(s) From table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
Example:
Example:
The following SQL statement selects all employees details which are common in Employees table as well as in Department table.
select * from Employee_J inner join Department_J on
Employee_J.E_DeptID=Department_J.E_DeptID;
Screenshot of the output given below:
- Left Join
The LEFT JOIN keyword returns all records from the left table (Employee table), and the matching records from the right table (Department table). The result is 0 records from the right side, if there is no match.
Syntax:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Example:
The following SQL statement returns all employees details and matching records from the Department table.
select * from Employee_J left join Department_J on Employee_J.E_DeptID=Department_J.E_DeptID;
Screenshot of the output given below:
- Right Join
The RIGHT JOIN keyword returns all records from the right table ‘Department table’, and the matching records from the left table ‘Employees table’ . The result is 0 records from the left side, if there is no match.
In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
Syntax:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Example:
The following SQL statement returns all department details and matching records from the employee table.
select * from Employee_J right join Department_J on Employee_J.E_DeptID=Department_J.E_DeptID;
Screenshot of the output given below:
Given below the example of joins with where condition
select * from Employee_J inner join Department_J on Employee_J.E_DeptID=Department_J.E_DeptID where E_Name=’Madhavi’;
Screen shot of the above sql statement has been given below
- Full Outer Join
The Full Outer Join keyword returns all records when there is a match in left table ‘Employees table’ or right table ‘Department table’ records.
Note: FULL OUTER JOIN and FULL JOIN are the same.
Syntax:
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name
WHERE condition;
Example:
select * from Employee_J Full outer join Department_J on Employee_J.E_DeptID=Department_J.E_DeptID;
The above query returns all records when there is a match in Employee table or Department table.
Screen shot of the output is given below
