Why are JOIN queries used in SQL?

JOIN queries are used to combine rows from two or more tables based on a related column between them. This allows retrieving combined data from multiple tables in a single result set.

Main types of JOIN:

• INNER JOIN: Returns rows where there is a match 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 in the right table, it returns NULL for the right table's columns.
• RIGHT JOIN (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, it returns NULL for the left table's columns.
• FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in one of the tables. If there is no match, it returns NULL for the columns of the table without a match.
• CROSS JOIN: Returns the Cartesian product of rows from the two tables (each row from the first table is combined with each row from the second).

Example of INNER JOIN:

SELECT
    c.customer_name,
    o.order_date
FROM
    customers c
INNER JOIN
    orders o
ON
    c.customer_id = o.customer_id;