Joins in SQL
Joins allow us to combine data from two or more tables based on Primary and Foreign keys. In this article, we will learn how Joins in SQL are implemented.
Though join concepts are the same in all databases, here we will learn about joins in Oracle. If you are new to Oracle, you can install Oracle and try these joins.
Tables used in this tutorial:
DeptNew: contains details of the department in an organization. It has 6 rows.
EmpNew: table contains the Employee details. Each employee has a JobID and a Manager. Employees works in various departments. Two of the employees Sam and Raj are not assigned to any department.
SalaryNew: This table provide details of the various Jobs and Salaries assigned to them.
If you wish to recreate these tables with data, please use the link provided at the bottom of this page.
The structure of the tables :
Types of Joins in SQL :
There are mainly four types of Joins in SQL:
- Equi join/Inner Join
- Outer join
- Cartesian join
- Self join
Equi join/Inner Join
When two tables are joined together using the equality of values in one or more columns, they make an equi join. The equi join or Inner join will display the matching rows in two tables.
Consider the before mentioned tables, EmpNew and DeptNew to answer the exercise below,
Exercise1. List the Employees – EmpNo, Ename, Deptno and DName.
Why do we need a join for this exercise:
EmpNo, Ename and Deptno are in EmpNew table but department name (DName) is in the DeptNew table. To get the department name, we need to join the two tables.
Query:
Select EmpNo, Ename, e.Deptno, Dname
From EmpNew e, DeptNew d /* e and d are table alias */
Where e.DeptNo = d.DeptNo; /* Join Condition */
Note: e.Deptno is qualifying deptno with the table name because Deptno column is present in both the tables. If Deptno is not qualified, oracle cannot decide from which table to pick Deptno and will give column ambiguously defined error.
Output:
This join query only displayed 5 rows, because equi join displays the matching/equal rows from both the tables and in this case only 5 in Deptno between the two tables match.
Multiple tables can be joined using the AND operator.
Below query will use Deptnew, EmpNew and SalaryNew table to display Employees, and their Departments and Salary Information.
Select Empno, Ename, e.Deptno, Dname, Salary, JobTitle
From EmpNew e, DeptNew d, SalaryNew s /* 3 tables are joined */
Where e.Deptno = d.deptno
AND e.JobID = s.JobID
Order By Empno; /* Order By clause is used to sort the output by Empno */
Output:
Outer Join
If there are any values in one table that do not have corresponding values in the other, in an equi join, that row will not be selected. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that row will have Nulls.
Exercise2. Display the employees working in each department. Display the records even if no employees belong to that department.
Select Empno, Ename, e.Deptno, d.Deptno AS “Deptno from DeptNew”, Dname
From EmpNew e, DeptNew d
Where e.Deptno(+) = d.deptno;
Output:
In the above example, we have displayed the Deptno from both EmpNew and DeptNew tables.
The output of the above SQL shows that in two departments (Deptno 800 and 400) there are no employees.
Outer join displays the matching rows and also the rows which are not matching, as in this example.
This is also called Right Outer join i.e. all matching rows and all the rows from the right table will be displayed. Any non-matching rows in the left will be displayed as NULLs.
If the plus sign (+) is placed on the other side, then the employee details with no corresponding department name will be displayed with NULL values in Department columns.
Query:
Select Empno, Ename, e.Deptno, d.Deptno “Deptno from DeptNew”, Dname
From EmpNew e, DeptNew d
Where e.Deptno = d.deptno(+);
This is also called left join, it displays the matching rows from the two tables and also all the rows present in the left table. The non-matching rows in the right table will be displayed as null.
Cartesian/Cross Join
When no WHERE clause is specified, each row of one table joins every row of the other table. This results in a Cartesian product. For e.g. If Table A has 10 rows and Table B has 10 rows, Cartesian join will output 100 rows.
Cartesian joins should be avoided as they are resource intensive.
Exercise3. Display the records from EmpNew and SalaryNew table using the Cartesian join.
Query:
Select Empno, Ename, JobTitle
From EmpNew e, SalaryNew s ;
Output:
The EmpNew table has 7 rows, SalaryNew table has 4 rows. The above query will give you 7 * 4 = 28 rows.
Self Join
A table can join to itself too i.e. each row in a table is combined with itself and with every other row of the table. The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
In the EmpNew table, every employee has a manager, except for Empno 1 – John as he is the CEO. He is the manager of Empno 2, 4 and 7. Observe that Manager is also an employee for e.g. Empno 2 – Jim is a MGR of Empno 3 – Doug. Similarly, EmpNo 4 – Karen is a manager of Empno 6 – Sam.
Exercise4. Display the Employee name and his manager details.
Query:
Select Worker.Ename, Worker.Empno, Manager.Empno Manager_ID, Manager.Ename Manager_Name
From EmpNew Worker, EmpNew Manager /* copies of the same table */
Where Worker.Mgr = Manager.Empno; /* table joining to itself */
Output:
