What types of join queries exist in SQL?

There are four main types of JOIN queries in SQL:

1. INNER JOIN: Returns only the rows where there are matches in both tables based on the specified condition.

2. 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 the right table's columns.

3. 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 the left table's columns.

4. FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in one of the tables. If there are no matches, NULL is returned for the respective columns.

Syntax examples:

-- Example of INNER JOIN
SELECT
    *
FROM
    table1 t1
INNER JOIN
    table2 t2 ON t1.column = t2.column;

-- Example of LEFT JOIN
SELECT
    *
FROM
    table1 t1
LEFT JOIN
    table2 t2 ON t1.column = t2.column;

-- Example of RIGHT JOIN
SELECT
    *
FROM
    table1 t1
RIGHT JOIN
    table2 t2 ON t1.column = t2.column;

-- Example of FULL JOIN
SELECT
    *
FROM
    table1 t1
FULL JOIN
    table2 t2 ON t1.column = t2.column;

There is also CROSS JOIN, which returns the Cartesian product of two tables (each row from the first table is combined with each row from the second). It does not require an ON condition.

-- Example of CROSS JOIN
SELECT
    *
FROM
    table1 t1
CROSS JOIN
    table2 t2;