In SQL, joins are used to combine rows from two or more tables based on a related column between them. Here are the most common types of joins and their purposes:
INNER JOIN
An INNER JOIN returns rows when there is a match in both tables. If there is no match, the row is not returned.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
LEFT (OUTER) JOIN
A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
RIGHT (OUTER) JOIN
A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
FULL (OUTER) JOIN
A FULL JOIN returns all rows when there is a match in either left or right table. Rows without a match in either table will contain NULLs.
SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;
CROSS JOIN
A CROSS JOIN returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows from the two tables.
SELECT columns
FROM table1
CROSS JOIN table2;
SELF JOIN
A SELF JOIN is a regular join, but the table is joined with itself. It is used when a table needs to be joined with itself to query hierarchical data or compare rows within the same table.
SELECT A.columns, B.columns
FROM table AS A
INNER JOIN table AS B
ON A.common_column = B.common_column;
Use JOINS appropriately to retrieve the desired dataset from multiple tables in your SQL database.
