What types of JOINs exist in SQL?

In SQL, there are several types of JOIN:

• INNER JOIN (or simply JOIN): Returns only the rows where there are matches in both tables.

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

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If there are no matches in the right table, NULL is returned for its columns.

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

• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table. If there are no matches in the left table, NULL is returned for its columns.

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

• FULL JOIN (or FULL OUTER JOIN): Returns all rows from both tables. If there are no matches, NULL is returned for the columns of the missing table.

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

• CROSS JOIN: Returns the Cartesian product of rows from both tables. Each row from the first table is combined with each row from the second table. The ON condition is not required.

  SELECT *
  FROM table1
  CROSS JOIN table2;
  

For clarity, they can be represented as:

JOIN Type	Description
INNER JOIN	Common rows from both tables
LEFT JOIN	All rows from the left table + matching from the right
RIGHT JOIN	All rows from the right table + matching from the left
FULL JOIN	All rows from both tables (with NULL where there are no matches)
CROSS JOIN	Cartesian product (all combinations of rows)