SQL JOINS | INNER JOIN | LEFT JOIN | RIGHT JOIN | OUTER JOIN
In this video we have covered the concept of joins in SQL using few examples and SQL queries.
Whether you're preparing for interviews or diving into real-world projects,
or you're a beginner or looking to refresh your skills, understanding joins is crucial.
This video provides valuable insights and SQL queries to enhance your understanding.
Code used in the video:
create table employee (emp_id varchar(10), emp_name varchar(20), age int ,dept int)
create table department (id int, name varchar(15))
INSERT INTO employee (emp_id, emp_name, age, dept)
VALUES
('E001', 'John Doe', 30, 1),
('E002', 'Alice Smith', 28, 2),
('E003', 'Bob Johnson', 35, 3),
('E004', 'Emily Brown', 32, 2),
('E005', 'Michael Clark', 40, 1);
INSERT INTO department (id, name)
VALUES
(1, 'IT'),
(2, 'HR'),
(3, 'Finance'),
(4, 'Marketing');
create table project (id int, name varchar(10), employee_id varchar(10))
INSERT INTO project (id, name, employee_id)
VALUES
(101, 'Project A', 'E001'),
(101, 'Project A', 'E002'),
(102, 'Project B', 'E003'),
(102, 'Project B', 'E002'),
(103, 'Project C', 'E002');
select * from employee
select * from department
select * from project
-- Q. Find the employees who are not working in any project.
/*
Logic: Let's see it manually.
We see in project table only employee E001, E002, E003 are involved in any project and we have E004 and E005 not working.
But since it's a very samll dataset we could see it manually. For larget datasets we can use the concept of joins in SQL.
*/
-- if they want only those employees who are working in any project then we use inner join
-- inner join -- when we simply write join then it is inner join.
select *
from employee as e
inner join project p on e.emp_id = p.employee_id
-- but if they want all the employees irrespective of whether they are working in any project or not then we use left join.
-- left join
select *
from employee as e
left join project p on e.emp_id = p.employee_id
-- give me the name of the employees and their department details
select
e.emp_id as "Employee ID", e.emp_name as 'Employee Name', e.age as Employee_Age, d.name as Department
from employee e
join department d on e.dept = d.id
-- find the employee details, departments and their correponding projects
select e.emp_id as "Employee ID" , e.emp_name as 'Employee Name', e.age, d.name as Department, p.name as Project_Name
from employee e
left join department d on e.dept = d.id
join project p on e.emp_id = p.employee_id
-- we can see similar result other way around
select p.name as Project_Name, STRING_AGG(e.emp_name, ',') as 'Employees working in Project'
-- e.emp_id, e.emp_name, e.age, d.name as Department, p.name as Project_Name
from project p
left join employee e on e.emp_id = p.employee_id
group by p.name