What types of JOIN operators exist in SQL?

• INNER JOIN (or simply JOIN): Returns records that have matching values in both joined tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matching records from the right table. If there are no matches in the right table, NULL values are returned for the right table's columns.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matching records from the left table. If there are no matches in the left table, NULL values are returned for the left table's columns.
• FULL JOIN (or FULL OUTER JOIN): Returns all records where there is a match in the left or right table. If there is no match, NULL values are returned for the columns where there is no match.
• CROSS JOIN: Returns the Cartesian product of rows from the joined tables. Each row from the first table is combined with each row from the second table. No ON condition is necessary.
• SELF JOIN: This is a join of a table with itself. It requires the use of aliases to distinguish columns from different "copies" of the table.

Example code for INNER JOIN:

SELECT col1, col2
FROM table1
INNER JOIN table2 ON table1.common_col = table2.common_col;