SQL: Types of Join

 The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

Join is the widely-used clause in the SQL Server essentially to combine and retrieve data from two or more tables. In a real-world relational database, data is structured in a large number of tables and which is why, there is a constant need to join these multiple tables based on logical relationships between them.

Join statements looks like below.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

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
• 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 left or right table
• SELF JOIN:  It is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement
• CARTESIAN JOIN  / CROSS JOIN: Returns the Cartesian product of the sets of records from the two or more joined tables.

      

Click on the each link above for the detail info with example.