SQL JOIN
SQL JOIN
In SQL, the JOIN clause is used to merge records from multiple tables based on a common column. This operation helps fetch meaningful insights by correlating datasets stored across different tables in a relational database.
Operators in SQL can be categorized into several types:
- INNER JOIN - Retrieves records with matching values in both tables.
- LEFT JOIN (LEFT OUTER JOIN) - Retrieves all records from the left table and matching ones from the right table.
- RIGHT JOIN (RIGHT OUTER JOIN) - Retrieves all records from the right table and matching ones from the left table.
- FULL JOIN (FULL OUTER JOIN) - Retrieves all records from both tables, filling unmatched ones with NULLs.
- CROSS JOIN - Creates a Cartesian product by combining all records from both tables.
- SELF JOIN - Joins a table with itself.
1. INNER JOIN
Description:
An INNER JOIN returns only those records where there is a match between both tables.
Syntax:
SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;
Example:
Consider two tables:
Customers Table
| CustomerID | Name | City |
|---|---|---|
| 1 | Alice | Paris |
| 2 | Bob | London |
| 3 | Carol | Berlin |
Orders Table
| OrderID | CustomerID | Product |
|---|---|---|
| 101 | 1 | Laptop |
| 102 | 2 | Mobile |
| 103 | 4 | Tablet |
SELECT Customers.Name, Orders.Product FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Output:
| Name | Product |
|---|---|
| Alice | Laptop |
| Bob | Mobile |
Syntax of Creating a View
To define a view, use the following SQL statement:
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
Explanation:
- view_name: The name assigned to the virtual table.
- SELECT: Retrieves specific columns from the original table.
- FROM table_name:Specifies the source table(s).
- WHERE condition: Filters the result set (optional).
Example: Creating and Using a View
Consider an "Employees" table storing information about employees in a company:
Employees List
Below is the list of Employees with their details.
| Emp_ID | Name | Department | Salary | City |
|---|---|---|---|---|
| 101 | Alice | IT | 70000 | New York |
| 102 | Bob | HR | 50000 | Chicago |
| 103 | Carol | IT | 75000 | Seattle |
| 104 | Dave | Finance | 60000 | Boston |
2. LEFT JOIN (LEFT OUTER JOIN)
Description:
Returns all records from the left table and matching ones from the right. If no match is found, NULL is returned for the right table's columns.
Syntax:
SELECT table1.column1, table2.column2 FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column;
Example:
SELECT Customers.Name, Orders.Product FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Output:
| Name | Product |
|---|---|
| Alice | Laptop |
| Bob | Mobile |
| Carol | NULL |
3. RIGHT JOIN (RIGHT OUTER JOIN)
Description:
Fetches all records from the right table and matching ones from the left. If no match exists, NULL appears for missing left table values.
Syntax:
SELECT table1.column1, table2.column2 FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;
Example:
SELECT Customers.Name, Orders.Product FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Output:
| Name | Product |
|---|---|
| Alice | Laptop |
| Bob | Mobile |
| NULL | Tablet |
4. FULL JOIN (FULL OUTER JOIN)
Description:
Retrieves all records from both tables. If no match exists, NULL fills missing values.
Syntax:
SELECT table1.column1, table2.column2 FROM table1 FULL JOIN table2 ON table1.common_column = table2.common_column;
Example:
SELECT Customers.Name, Orders.Product FROM Customers FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Output:
| Name | Product |
|---|---|
| Alice | Laptop |
| Bob | Mobile |
| Carol | NULL |
| NULL | Tablet |
5. CROSS JOIN
Description:
Produces every possible combination of rows from both tables, resulting in a Cartesian product.
Syntax:
SELECT table1.column1, table2.column2 FROM table1 CROSS JOIN table2;
Example:
SELECT Customers.Name, Orders.Product FROM Customers CROSS JOIN Orders;
Output:
| Name | Product |
|---|---|
| Alice | Laptop |
| Alice | Mobile |
| Alice | Tablet |
| Bob | Laptop |
| Bob | Mobile |
| Bob | Tablet |
| Carol | Laptop |
| Carol | Mobile |
| Carol | Tablet |
6. SELF JOIN
Description:
A SELF JOIN relates a table to itself, commonly used to establish hierarchical relationships.
Syntax:
SELECT A.column1, B.column2 FROM table_name A JOIN table_name B ON A.common_column = B.common_column;
Example:
Consider an Employees table:
Employees Table
| EmpID | Name | ManagerID |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
| 4 | Dave | 2 |
SELECT A.Name AS Employee, B.Name AS Manager FROM Employees A LEFT JOIN Employees B ON A.ManagerID = B.EmpID;
Output:
| Employee | Manager |
|---|---|
| Alice | NULL |
| Bob | Alice |
| Carol | Alice |
| Dave | Bob |
Conclusion
SQL JOINs are essential for managing complex relational databases. Each type serves a unique purpose, enabling powerful data retrieval strategies. Whether you need to find matching records, include non-matching ones, or create cross-relations, mastering JOINs is crucial for efficient database querying.