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
Join can include two or more tables and would be based on a condition or group of conditions
In addition to the joins specified above there is one more join called cross join which is the simplest of the join operations and doesnt require a condition to be specified. It performs cartesian product of the involved tables and resultset will contain all possible combinations of rows between the tables.
Full join can be used to simulate all other kinds of joins by writing appropriate conditions in WHERE clause