All about Joints-SQL

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables.

Two tables should have common columns between them

Datatypes of columns should be the same, names not needed

select j.job_id , d.department_name, e.employee_id,e.salary

 from ((job_history

 inner join departments on j.department_id=d.department_id)

 inner join employees on e.department_id=d.department_id);

• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.

The LEFT JOIN keyword returns all rows from the left table , even if there are no matches in the right table.

Retrieves rows from right table, that match based on common key.

For non-matching rows, only left table columns are retrieved, with right table columns as NULL.

SELECT C.CustomerName, O.OrderID

FROM Customers

LEFT JOIN Orders

ON C.CustomerID=O.CustomerID

ORDER BY C.CustomerName;

• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.

For non-matching rows, only right table columns are retrieved, with left table columns as NULL.

Returns all rows from the right table , even if there are no matches in the left table .

Retrieves rows from Left table ,that match based on common key.

SELECT O.OrderID, E.LastName, E.FirstName

FROM Orders

RIGHT JOIN Employees

ON O.EmployeeID = E.EmployeeID

ORDER BY O.OrderID;

• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

Combination of LEFT and RIGHT OUTER JOINS.

Includes unmatched rows from both sides.

SELECT e.employee_id, e.first_name, e.last_name, d.department_name

FROM Employees e FULL OUTER JOIN Departments d

ON e.employee_id= d.manager_id;