Most commonly used SQL joins are INNER JOIN and LEFT OUTER JOIN and RIGHT OUTER JOIN.
The most commonly used SQL joins are:
- INNER JOIN: Retrieves records that have matching values in both tables. It returns only the rows where there is a match in both tables based on the specified condition.
- LEFT JOIN (or LEFT OUTER JOIN): Retrieves all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.
- RIGHT JOIN (or RIGHT OUTER JOIN): Retrieves all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table.
- FULL JOIN (or FULL OUTER JOIN): Retrieves all records when there is a match in either the left or the right table records. If there is no match, NULL values are returned for columns from the table without a match.
- CROSS JOIN: Returns the Cartesian product of the two tables, i.e., all possible combinations of rows from both tables. It does not require a specific condition for joining.
These joins are fundamental in combining data from different tables in SQL queries. The choice of which join to use depends on the specific requirements of the query and the relationships between the tables.