13. SQL Commands Part 4

SQL Commands Part 4(2)

There are three types of join-

• Inner Join or Equi Join

• Outer Join

  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join

• Self Join.

• Inner Join or Equi Join means the fields that are common in both the tables.

• Left Outer Join contains all the records from the table that is on the the left side of the query and the common field from both the tables.

• Right Outer Join contains all the records from the table that is on the the right side of the query and the common field from both the tables.

• Full outer join means display all the record from both the table.

Using inner join for the first time

code: select e.employee_id, e.first_name, e.salary, e.department_id, d.department_name from employees e inner join department d on e.department_id=d.department_id;

[Note: e.employee_id means employee_id should be selected from employee table and d.department_name means department_name should be selected from department table. We have to mention that e is employee table and d is department table in this line "employees e inner join department d". Also we have to mention the common field like this "e.department_id=d.department_id"]

[Note: Another way of writing the above code.]

code: select e.employee_id, e.first_name, e.salary, e.department_id, d.department_name from employees e inner join department d where e.department_id=d.department_id;

[In the previous query there were 107 rows but in the latest query output there are 106 rows this is because 1 employee department is null.]

Display those employees that are working in sales department?

code: select e.employee_id, e.first_name, e.salary, e.department_id, d.department_name from employees e, department d where e.department_id=d.department_id where d.department_name like 'Sales';

1. Using left outer join

code: select e.employee_id, e.first_name, e.salary, e.department_id, d.department_name from employees e left join department d on e.department_id=d.department_id;

[Note: It will contain all records from the left table i.e. the employees table and the common records from departments table. See this line "employees e left join department d" since employee table is on the left side of left join that is why it contains all records of employee table and common records from department table.]

2. using left outer join

code: select e.employee_id, e.first_name, e.salary, e.department_id, d.department_name from employees e left join department d on e.department_id=d.department_id;

[Note: It will contain all records from the left table i.e. the employees table and the common records from departments table. See this line "employees e left join department d" since employee table is on the left side of left join that is why it contains all records of employee table and common records from department table.]

Using right outer join

code: select e.employee_id, e.first_name, e.salary, d.department_id, d.department_name from department d right join employees e on d.department_id=e.department_id;

[Note: This is taking all the records from employees table and the common records from department table.]

Using Full outer join

code: select e.employee_id, e.first_name, e.salary, d.department_id, d.department_name from department d full join employees e on d.department_id=e.department_id;

Display all column names of location table.

code: desc locations

Display the record of those employees who are working in Oxford city?

code: select e.employee_id, e.first_name, e.salary, d.department_name, l.city from employees e inner join department d on d.department_id=e.department_id inner join locations l on d.location_id=l.location_id where l.city like 'Oxford';

[Note: For this query we are making use of three tables employees, department and location we are using inner join to connect all three tables. Especially this line is used to connect location table to department table
"d.department_id=e.department_id inner join locations l on d.location_id=l.location_id where l.city like 'Oxford';" ]

Tell me who is the manager of employee whose ename is b

Ans: The manager of b is a since b's manager_id is 1.

[Note: Manager_id is nothing but employee_id.]

Who is the manager of who in this image?

Ans: Nikhil's manager is Sanjay, Dipanwita's manager is Nikhil & Avhishek's manager is Dipanwita.

Display employee details and their manager id?

Ans: select employee_id, first_name, manager_id from employees;

Display the manager name of each employee.

code: select e.employee_id, e.first_name, e.manager_id, m.first_name "manager Name" from employees e inner join employees m on e.manager_id=m.eployee_id order by e.employee_id;

[Note: To display the managers name and manager_id side by side we are using a trick of copying the same table and comparing them side by side.

You see what we are doing is we are comparing the manager_id with empid and getting the managers name but this happens only because there is a second or virtual table at the side of actual table. From the above query you can guess that employees m is the virtual table of employees e.]