Joins in SQL

What Are SQL Joins?

Relational databases normally work with normalized data, meaning that the data is split into multiple tables to remove redundancy and enable scalability. However, you will use SQL joins whenever you want to bring together data that isRelated — for example, customers and their orders.

Using SQL JOIN to Combine Rows of 2 or More tables using a related column

Imagine you have two tables:

Customers (customer_id, name, email)

Orders (order_id, customer_id, amount)

You join these tables by a common column, which in this case is customer_id, to visualize which customer made what order.

In the absence of joins, SQL queries could only read from one table at a time. Joins are what truly unlock the power of relational databases:

✔ Joint-related data across multiple tables

✔ Reduce redundancy

✔ Support powerful analytics

✔ Enable complex decision-making

✔ Improve database design integrity

In short, joins are the backbone of relational queries.

Types of SQL Joins Explained

There are several types of joins, each serving a unique purpose:

1. INNER JOIN
2. 
   
3. LEFT JOIN (LEFT OUTER JOIN)
4. 
   
5. RIGHT JOIN (RIGHT OUTER JOIN)
6. 
   
7. FULL JOIN (FULL OUTER JOIN)
8. 
   
9. CROSS JOIN
10. 
    
11. SELF JOIN
12. 
    
13. NATURAL JOIN

1. INNER JOIN

The most commonly used join. It returns only the rows where there is a match in both tables.

SELECT columns

FROM table1

INNER JOIN table2

ON table1.common_column = table2.common_column;

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table, and the matched records from the right table. If no match exists, results from the right table will be NULL.

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.common_column = table2.common_column;

3. RIGHT JOIN (RIGHT OUTER JOIN)

Opposite of LEFT JOIN. Returns all records from the right table, and matched records from the left table.

SELECT columns

FROM table1

RIGHT JOIN table2

ON table1.common_column = table2.common_column;

4. FULL JOIN (FULL OUTER JOIN)

Returns all records from both tables, matching where possible. Where no match exists, the result shows NULLs.

SELECT columns

FROM table1

FULL JOIN table2

ON table1.common_column = table2.common_column;

5. CROSS JOIN

Produces the Cartesian product between two tables — every combination of rows.

SELECT *

FROM table1

CROSS JOIN table2;

6. SELF JOIN

This is a join where a table joins to itself. Useful when comparing rows within the same table.

SELECT e1.name AS Employee, e2.name AS Manager

FROM Employees e1

LEFT JOIN Employees e2

ON e1.manager_id = e2.employee_id;

7. NATURAL JOIN

Automatically joins tables based on columns with the same names.

SELECT *

FROM Students

NATURAL JOIN Results;

Do visit our channel to know more: SevenMentor

Author:-

Shivsharan Kunchalwar