Database Joins| SQL Joins| Types of database Joins

Join actually puts data from two or more tables into a single result set.

Types:
Inner join or Equi join
Outer Join
Left outer Join
Right Outer Join
Full Outer Join
Cross Join
Self Join

Inner Join: An inner join returns all rows that result in a match.
Example: SELECT Employees.Name, Orders.ProductFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID

Left Outer Join: A Left join returns all rows of the left of the conditional even if there is no right column to match.
Example:SELECT Employees.Name, Orders.ProductFROM EmployeesLEFT JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID

Right Outer Join: A right join will display rows on the right side of the conditional that may or may not have a match.
Example:SELECT Employees.Name, Orders.ProductFROM EmployeesRIGHT JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID

Full Outer Join: Returns all the left, right (unmatched rows) and also matched rows.

Cross-Join: A cross join (or Cartesian Product join) will return a result table where each row from the first table is combined with each row from the second table. The number of rows in the result table is the product of the number of rows in each table.

Self-Join: A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table.
Example:
SELECT DISTINCT c1.ContactName, c1.Address, c1.City, c1.Region
FROM Customers AS c1, Customers AS c2
WHERE c1.Region = c2.Region
AND c1.ContactName <> c2.ContactName
ORDER BY c1.Region, c1.ContactName;

Tags: Database Joins, Sql joins, Inner join, Equi join, Outer Join, Left outer Join, Right Outer Join, Full Outer Join, Cross Join, Self Join, Types of Database Joins