SQL JOIN - Inner join, outer join, full, cross, self join
What is SQL Join?
- The SQL join clause uses to combine two or more tables in a database.
- The joining happen based on the exact column match between the tables.
BASIC JOIN - Syntax
SELECT Table1.column-name1 Table1.column-name2 Table2.column-name1..... FROM Table-name1 JOIN Table-name2 ON Table1.column =Table2.column;
There are four main types of join available for our use.
|INNER JOIN||Selects the rows only when there are having the same column field in both tables.|
|OUTER JOIN||3 types of Outer join exists depending on non-matching rows from left side table, right side table or both the tabkes|
|SELF JOIN||This join is used to join the table with itself.|
|CROSS JOIN||This join gives the Cartesian product of sets of rows from the two joined tables.|
The OUTER JOIN is divided into three types.
|LEFT OUTER JOIN||Selects all rows from the left table and |
only matching rows from the right table.
|RIGHT OUTER JOIN||Selects all rows from right table and |
only matching rows from the left table.
|FULL OUTER JOIN||Selects all the rows from both left and right tables, and |
the resulting table will have the NULL values in the rows for which there is no matching column.