What is SQL JOIN?

SQL JOIN is used to combine rows from two or more tables based on a related column between them.

Main types of JOIN:

• INNER JOIN: Returns rows when there are matches in both tables.
• LEFT 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: 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 OUTER JOIN: Returns all rows when there is a match in one of the tables. It returns all rows from both tables, filling with NULL where there are no matches.

Example of INNER JOIN:

SELECT
    Orders.OrderID,
    Customers.CustomerName
FROM
    Orders
INNER JOIN Customers
    ON Orders.CustomerID = Customers.CustomerID; // Joining tables on a common column

Example of LEFT JOIN:

SELECT
    Customers.CustomerName,
    Orders.OrderID
FROM
    Customers
LEFT JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID // Returns all customers, even if they have no orders
ORDER BY
    Customers.CustomerName;