How does SQL Join work?

SQL JOIN combines rows from two or more tables based on a related column between them. There are several types of JOIN:

• INNER JOIN: Returns rows where there are matching values in both tables.
• LEFT (OUTER) JOIN: Returns all rows from the left table and matching rows from the right. If there are no matches in the right table, NULL values are returned for the right table's columns.
• RIGHT (OUTER) JOIN: Returns all rows from the right table and matching rows from the left. If there are no matches in the left table, NULL values are returned for the left table's columns.
• FULL (OUTER) JOIN: Returns all rows from both tables. If there are no matches, NULL values are returned for the respective columns.
• CROSS JOIN: Returns the Cartesian product of the two tables (each row from the first table is combined with each row from the second).

The syntax usually looks like this:

SELECT column1, column2...
FROM table1
JOIN table2 ON table1.common_column = table2.common_column;

Example of INNER JOIN:

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Consider the tables customers and orders:

customers

customer_id	customer_name
1	Alice
2	Bob
3	Charlie

orders

order_id	customer_id	amount
101	1	100
102	2	150
103	1	200
104	4	50

The result of INNER JOIN will be:

order_id	customer_name
101	Alice
102	Bob
103	Alice

The result of LEFT JOIN (orders LEFT JOIN customers):

order_id	customer_id	amount	customer_name
101	1	100	Alice
102	2	150	Bob
103	1	200	Alice
104	4	50	NULL