Imagine SQL as a cricket team then Joins is like M S Dhoni. it is the most important segment in any analysis of the type of project you are handling.
Basically, 6 types of joins will be mostly used in your day-to-day activities
Problem Statement:
(i) Retrieve a list of employees along with their department information
(ii) showing only those employees who belong to a department.
here we have the employees’ table and department table
note:
- if you are performing join on 2 tables both columns must have the same column entity
2. column names can be different
for instance, in real life, you cannot compare measurements with meters with liters, and weight with liters. you should use the same entity to join.
select emp_id, emp_name, department_name employees Inner join departments on employees.emp_department_id =
department.department_id
Problem Statement: Display a list of all employees and their corresponding salary information, — including those employees who do not have a salary entry.
Select employees.emp_id, emp_name, salary_amount from employees LEFT JOIN salaries on employees.emp_id = salaries.emp_id;
Problem Statement: Generate a report of all salary records along with the employee information, — ensuring that all salary entries are included, — and display NULL for employees without a salary record.
SELECT salary_amount, employees.emp_id, emp_name FROM salaries RIGHT JOIN employees ON employees.emp_id = salaries.emp_id;
- Problem Statement: Create a comprehensive report that includes both employees and project information,
- indicating the project lead for each project.
- Include all employees and projects, even if they are not currently assigned to a project.
SELECT project_name, emp_id, emp_name FROM employees FULL OUTER JOIN projects ON employees.emp_id = projects.project_lead_id;
Problem Statement: Generate a matrix of all possible combinations of employees and departments,
— regardless of whether there is an actual assignment of employees to departments.
— This is useful for exploring all possible pairings.
Select emp_id, emp_name , department_name FROM Employees Cross Join Departments
