How does SQL Join work?

SQL JOIN combines rows from two or more tables based on a related column between them. The types of JOIN determine which rows are included in the result set:

• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table. If there are no matches in the right table, the fields from the right table will be NULL.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table. If there are no matches in the left table, the fields from the left table will be NULL.
• FULL JOIN (or FULL OUTER JOIN): Returns all rows where there is a match in one of the tables. The combined result set contains all rows from both tables, filling NULL where there are no matches.
• CROSS JOIN: Returns the Cartesian product of the two tables. The result set contains all possible combinations of rows from both tables.

Syntax:

SELECT column_name(s)
FROM table1
JOIN_TYPE table2
ON table1.column_name = table2.column_name;

Example of INNER JOIN:

Suppose we have tables Orders (order id, customer id) and Customers (customer id, customer name).

Table Orders:

order_id	customer_id
1	101
2	102
3	101
4	103

Table Customers:

customer_id	customer_name
101	Alice
102	Bob
104	Charlie

INNER JOIN query:

SELECT Orders.order_id, Customers.customer_name
FROM Orders
INNER JOIN Customers ON Orders.customer_id = Customers.customer_id;

Result:

order_id	customer_name
1	Alice
2	Bob
3	Alice
4	NULL

Corrected INNER JOIN result:

order_id	customer_name
1	Alice
2	Bob
3	Alice