JOINs are used to combine rows from two or more tables based on a related column between them. They help retrieve meaningful data from multiple tables.
1. INNER JOIN – Matching Records in Both Tables
Returns only the rows that have matching values in both tables.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;Example:
SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;Output:
2. LEFT JOIN (or LEFT OUTER JOIN) – All from Left + Matches from Right
Returns all rows from the left table, and matched rows from the right. If no match, returns NULL.
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;Example:
SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;Output:
3. RIGHT JOIN (or RIGHT OUTER JOIN) – All from Right + Matches from Left
Returns all rows from the right table, and matched ones from the left. If no match, fills with NULL.
Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;Example:
SELECT orders.id, customers.name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;Output:
4. FULL JOIN / FULL OUTER JOIN – All from Both Tables
Returns all records when there is a match in either the left or right table. Non-matching rows will have NULL.
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;Example:
SELECT *
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
UNION
SELECT *
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;Output:
5. CROSS JOIN – All Possible Combinations
Returns the Cartesian product of two tables — every row in the first table is combined with every row in the second.
Syntax:
SELECT columns
FROM table1
CROSS JOIN table2;Example:
SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;Output:
6. SELF JOIN – Join a Table to Itself
Used when you want to compare rows in the same table. You use aliases to treat one table like two.
Syntax:
SELECT A.column1, B.column2
FROM table_name AS A
JOIN table_name AS B
ON A.related_column = B.related_column;Example:
SELECT A.name AS Employee, B.name AS Manager
FROM employees A
JOIN employees B ON A.manager_id = B.id;Output:
7. NATURAL JOIN – Auto-Match Columns with Same Name
Automatically joins tables on columns with the same name and compatible data types — no ON clause required.
Syntax:
SELECT columns
FROM table1
NATURAL JOIN table2;Example:
SELECT *
FROM students
NATURAL JOIN marks;Output:
