SQL JOIN Operations

In SQL, a JOIN operation combines rows from two or more tables based on a related column between them. Joins are used to retrieve data from multiple tables that have a relationship defined by a common column. Here are the different types of joins commonly used in SQL:

1. INNER JOIN:

Returns only the rows that have matching values in both tables.

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

2. LEFT JOIN (or LEFT OUTER JOIN):

Returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table.

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

3. RIGHT JOIN (or RIGHT OUTER JOIN):

Returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the left table.

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

4. FULL JOIN (or FULL OUTER JOIN):

Returns all rows from both tables, matching rows where possible and filling NULL values where no match is found.

SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column;

5. CROSS JOIN:

Returns the Cartesian product of the two tables, resulting in all possible combinations of rows from both tables. It does not require a matching column.

SELECT * FROM table1 CROSS JOIN table2;

These are the main types of joins in SQL, allowing you to combine and retrieve data from multiple tables based on their relationships.