?? Day 30: Exploring Practical SQL Implementation - Part 2??

SQL Joins Commands:

• In SQL, a JOIN operation combines rows from two or more tables based on a related column between them.
• Joins are fundamental to querying and retrieving data from multiple tables, allowing for a more comprehensive analysis of data across different entities.
• The JOIN clause specifies the condition to determine how rows should be combined.

SELECT t1.*, t2.*
FROM table1 t1
JOIN table2 t2 ON t1.column_name = t2.column_name;        

• SELECT: Specifies the columns to be retrieved.
• FROM: Specifies the tables involved in the operation.
• JOIN: Specifies the type of join (INNER, LEFT, RIGHT, FULL).
• ON: Specifies the condition for matching rows.

Common Types of Joins:

1. INNER JOIN:

• Returns rows when there is a match in both tables based on the specified condition.

2. LEFT JOIN (or LEFT OUTER JOIN):

• Returns all rows from the left table and matching rows from the right table. If no match is found,
• NULL values are returned for columns from the right table.

3. RIGHT JOIN (or RIGHT OUTER JOIN):

• Returns all rows from the right table and matching rows from the left table. If no match is found,
• NULL values are returned for columns from the left table.

4. FULL JOIN (or FULL OUTER JOIN):

• Returns all rows when there is a match in either the left or right table. If no match is found,
• NULL values are returned for columns from the table without a match.

Joins in Real life Examples

Let's consider a scenario where we have two tables in a database - employees and departments.

*Table:employees*
+-------------+--------------+--------+
| employee_id | employee_name| dept_id|
+-------------+--------------+--------+
| 1           | Jignesh       | 101    |
| 2           | Upendra       | 102    |
| 3           | Rajan         | 101    |
| 4           | Krishna       | 103    |
+-------------+--------------+--------+

*Table:departments*
+--------+------------------+
| dept_id| department_name  |
+--------+------------------+
| 101    | HR               |
| 102    | IT               |
| 103    | Finance          |
+--------+------------------+        

1. INNER JOIN:

• Query:

SELECT employees.*, departments.*
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;        

• Explanation: Only records with matching dept_id values in both tables will be included in the result.
• Result:

+-------------+--------------+--------+--------+------------------+
| employee_id | employee_name| dept_id| dept_id| department_name  |
+-------------+--------------+--------+--------+------------------+
| 1           | Jignesh       | 101    | 101    | HR               |
| 2           | Upendra       | 102    | 102    | IT               |
| 3           | Rajan         | 101    | 101    | HR               |
| 4           | Krishna       | 103    | 103    | Finance          |
+-------------+--------------+--------+--------+------------------+        

2. LEFT JOIN:

• Query:

SELECT employees.*, departments.*
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;        

• Explanation: All records from the left table (employees) will be included, and matching records from the right table (departments) will be added.
• Result:

+-------------+--------------+--------+--------+------------------+
| employee_id | employee_name| dept_id| dept_id| department_name  |
+-------------+--------------+--------+--------+------------------+
| 1           | Jignesh       | 101    | 101    | HR               |
| 2           | Upendra       | 102    | 102    | IT               |
| 3           | Rajan         | 101    | 101    | HR               |
| 4           | Krishna       | 103    | 103    | Finance          |
+-------------+--------------+--------+--------+------------------+        

3. RIGHT JOIN:

• Query:

SELECT employees.*, departments.*
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;        

• Explanation: All records from the right table (departments) will be included, and matching records from the left table (employees) will be added.
• Result:

+-------------+--------------+--------+--------+------------------+
| employee_id | employee_name| dept_id| dept_id| department_name  |
+-------------+--------------+--------+--------+------------------+
| 1           | Jignesh       | 101    | 101    | HR               |
| 2           | Upendra       | 102    | 102    | IT               |
| 3           | Rajan         | 101    | 101    | HR               |
| NULL        | NULL          | NULL   | 103    | Finance          |
+-------------+--------------+--------+--------+------------------+        

4. FULL JOIN:

• Query:

SELECT employees.*, departments.*
FROM employees
FULL JOIN departments ON employees.dept_id = departments.dept_id;        

• Explanation: All records from both tables will be included, showing matches where available and NULL where there is no match.

• Result:

+-------------+--------------+--------+--------+------------------+
| employee_id | employee_name| dept_id| dept_id| department_name  |
+-------------+--------------+--------+--------+------------------+
| 1           | Jignesh       | 101    | 101    | HR               |
| 2           | Upendra       | 102    | 102    | IT               |
| 3           | Rajan         | 101    | 101    | HR               |
| 4           | Krishna       | 103    | 103    | Finance          |
| NULL        | NULL          | NULL   | 103    | Finance          |
+-------------+--------------+--------+--------+------------------+