What types of JOINs exist in SQL and how do left join, right join, and inner join differ?

In SQL, there are several main types of joins that allow you to combine rows from two or more tables based on a specific condition:

• INNER JOIN — returns only the rows where there are matches in both tables.
• LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the left table and the matching rows from the right table; if there are no matches, NULLs are used.
• RIGHT JOIN (RIGHT OUTER JOIN) — similar to LEFT JOIN, but returns all rows from the right table.
• FULL JOIN (FULL OUTER JOIN) — returns all rows from both tables, filling in NULLs where there are no matches.
• CROSS JOIN — Cartesian product of two tables, each row of the first table is joined with every row of the second.

Example of INNER JOIN:

SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

In SQL, there are several types of JOINs that allow combining rows from two tables based on a specific condition:

• INNER JOIN — returns only the rows where there are matches in both tables.
• LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the left table and matching rows from the right table. If there are no matches, the right table columns will contain NULL.
• RIGHT JOIN (RIGHT OUTER JOIN) — returns all rows from the right table and matching rows from the left table, with NULL where there are no matches.

Example:

Table A:

id	name
1	Alice
2	Bob

Table B:

id	city
1	London
3	Berlin

• INNER JOIN on id will return only id=1 (Alice and London).
• LEFT JOIN will return:
  • id=1 (Alice, London)
  • id=2 (Bob, NULL)
• RIGHT JOIN will return:
  • id=1 (Alice, London)
  • id=3 (NULL, Berlin)

Thus, LEFT and RIGHT JOINs keep all rows from one table, supplementing with data from the other, while INNER JOIN returns only matching rows.