JOIN: LEFT/RIGHT/INNER/OUTER

SQL Joins

SQL JOIN is an operation that allows combining data from multiple tables based on related columns.

Main types of JOIN INNER

• JOIN – returns only rows that have matches in both tables.
• LEFT JOIN (LEFT OUTER JOIN) – returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN) – returns all rows from the right table and matching rows from the left table.
• FULL JOIN (FULL OUTER JOIN) – combines
• LEFT JOIN and RIGHT JOIN – returns all rows from both tables.

Additional JOINs CROSS JOIN – Cartesian product: each row from the first table is joined with all rows from the second table. SELF JOIN – a table joins itself. NATURAL JOIN – automatically joins tables by columns with the same names (not recommended).

Examples:

1. INNER JOIN Returns only matching records:

SELECT users.id, users.name, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id;

2. LEFT JOIN All users + their orders (if any):

SELECT users.id, users.name, orders.amount FROM users LEFT JOIN orders ON users.id = orders.user_id;

3. RIGHT JOIN All orders + users (if any):

SELECT users.id, users.name, orders.amount FROM users RIGHT JOIN orders ON users.id = orders.user_id;

4. FULL JOIN All users and orders, even if they are not linked:

SELECT users.id, users.name, orders.amount FROM users FULL JOIN orders ON users.id = orders.user_id;

5. CROSS JOIN Cartesian product:

SELECT users.name, products.name FROM users CROSS JOIN products;